~/learn/SQL · Dates & text
CASE and conditional aggregation
Bucket values inline, then count each bucket in one pass.
why this matters
CASE is SQL's if/else — labelling orders as 'big' or 'small', or counting only certain rows inside a SUM.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
CASE is SQL's if/else. It walks the WHEN branches in order and returns the first match, falling back to ELSE.
Try it: Move the premium threshold down to 300.
Putting CASE inside an aggregate is the pivot trick: each expression counts or sums only the rows matching its condition, so you get one column per category in a single scan.
The same pattern gives you rates: divide a conditional count by the total count.
key takeaways
- CASE WHEN … THEN … ELSE … END returns a value.
- SUM(CASE WHEN … THEN 1 ELSE 0 END) counts matches.
- Always add ELSE, or you'll get NULL.
check your understanding
What does a CASE return when no WHEN matches and there's no ELSE?
free practice
Build confidence with a question you can solve without Pro.