0

I’ve typed out the query below and I get a syntax error after the first when of the the CASE part. Please help correct.

SELECT state.name, country.name, AVG(state_weather_stats.humidity) AS average_monthly_humidity,
CASE AVG(state_weather_stats.temperature)
    WHEN >=0 AND < 15 THEN “COLD”
    WHEN >= 15 AND <30 THEN “WARM”
    ELSE “HOT”
END AS weather_type
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
triedit
  • 169
  • 1
  • 9

1 Answers1

1

If you're using inequalities, you can't use the CASE <expr> WHEN ... syntax. That syntax is only usable when you're doing equality comparisons.

So you can use the CASE WHEN <expr> ... syntax instead:

SELECT state.name, country.name, AVG(state_weather_stats.humidity) AS average_monthly_humidity,
CASE 
    WHEN average_monthly_humidity >= average_monthly_humidity THEN 'COLD'
    WHEN average_monthly_humidity >= 15 AND average_monthly_humidity <30 THEN ...something...
    ELSE 'HOT'
END AS weather_type

I don't know what you want where I put "...something..." but you do need a THEN clause before the ELSE.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I fixed that and now I have another error: ERROR 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains non aggregated column state.name from the line GROUP BY state.name, country.name – triedit Feb 21 '21 at 21:32
  • That's a separate question, but I have a highly upvoted answer to explain that error: https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e/13999903#13999903 – Bill Karwin Feb 21 '21 at 21:53