CODEWITHSUNDEEP
mysqlsqlsyntax
triedit
triedit

Reputation: 183

How to write proper MySQL case statement

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

Upvotes: 0

Views: 249

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 1

Related Questions