CASE WHEN Part 2 — Teaching Tech Tools

Note that a CASE WHEN can (and usually does) have multiple comparisons, not only one of two categories:

SELECT *, CASE WHEN 2023 estimate > 2000000 THEN ‘Above 2 mn‘

WHEN 2023 estimate > 1000000 THEN ‘Above 1 mn‘

ELSE ‘Less THAN 1 mn‘ END as ‘Population Category‘

FROM database_table

Now there’s several comparisons in the CASE WHEN, first we check if the estimate column is above 2 million and if so, set the new column to ‘Above 2 mn’. But if that case does not hit, then we’ll add another check to see if the estimate is above 1 million and otherwise, will be below 1 million.
One small nuance is that if there’s multiple cases in a CASE WHEN statement, we only write CASE one time, then each additional case will have only the WHEN statement on each line.