P09 · SQL & Querying
SQL CASE — conditional aggregation done right
How SQL CASE and conditional aggregation work — and the trap where COUNT of a CASE with an ELSE 0 counts every row instead of the ones you flagged.
Start with the basics
A CASE expression is SQL's if/else: it checks conditions in order and returns the value from the first one that matches, per row.
Its most common job is conditional aggregation — wrap a CASE in a sum to total only the rows that match a condition, which is how you build a pivot or a segmented metric.
It reads plainly, and the traps are subtle: what a CASE returns when nothing matches, and the difference between summing a flag and counting one.
One that works
| region | amount |
|---|---|
| N | 100 |
| S | 200 |
| N | 50 |
SELECT SUM(CASE WHEN region = 'N'
THEN amount ELSE 0 END) AS north
FROM sales;| north |
|---|
| 150 |
The CASE flags North rows with their amount and others with 0; summing gives North's total.
CASE returns a value per row based on conditions; wrapped in SUM it totals only the rows you flag.
When to use it
- Reach for CASE to bucket rows, or to total only the rows meeting a condition.
- Wrap a CASE in SUM for a conditional total; be deliberate about the ELSE.
- Order overlapping conditions narrowest-first so the right branch wins.
That flagging is exactly where a count goes wrong. Count a CASE that falls back to 0, and you count every row, because 0 is a value — a sum of the flags is what you meant. Here is the day that bites.
See it happen
The count that counts everything — COUNT vs SUM of a CASE
You want the number of North sales. You wrote a CASE with an ELSE 0 and counted it. Watch COUNT count the zero rows too.
| region | amount |
|---|---|
| N | 100 |
| S | 200 |
| N | 50 |
COUNT of the CASE — counts the zeros
SELECT COUNT(CASE WHEN region = 'N'
THEN 1 ELSE 0 END) AS n
FROM sales;| n |
|---|
| 3 |
SUM of the flags — counts the matches
SELECT SUM(CASE WHEN region = 'N'
THEN 1 ELSE 0 END) AS n
FROM sales;| n |
|---|
| 2 |
The 'North sales count' reads 3, not 2 — counting counts the ELSE 0 rows too, because 0 is a value, not a blank; summing the flags adds only the matches.
Walk the full example in the unit →The rest of the family
More ways CASE catches you out
A CASE without an ELSE yields a blank, which a sum ignores (usually what you want) and a column-count also ignores — which is what makes counting a CASE a conditional counter.
A CASE expression can group rows into buckets or drive the sort order.
Where supported, an aggregate with a FILTER clause is a tidier way to write conditional aggregation.
A CASE stops at the first branch that matches, so overlapping conditions must be ordered narrowest-first or a wide branch swallows the edge.
The short CASE form compares with equals, so it never matches a missing value; only the longer searched form can.
Self-check
How to spot it in your own SQL
- A conditional count equals the total row count.
- You wrote COUNT of a CASE that has an ELSE 0.
- Overlapping CASE conditions and the wrong branch is winning.
- A short CASE never matches your blank rows.
What people miss
Conditional counting looks trivial until a stray ELSE 0 turns a count into a row count. The flag is a value, not a blank, so counting it counts everything — a plausible number that is quietly wrong.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why does my COUNT of a CASE count every row?
With an ELSE 0, every row gets a value (1 or 0), and counting counts any value — including the zeros — so you get the total row count.
Sum the flags instead, or drop the ELSE so non-matching rows are blank and skipped by the count.
from the unit →What does a CASE return when nothing matches?
Without an ELSE, a CASE returns a blank for non-matching rows — which a sum ignores (usually what you want) and a column-count also ignores.
That's exactly why a CASE with no ELSE makes a clean conditional counter.
from the unit →Why is the wrong CASE branch winning?
A CASE takes the first branch that matches, so if a wide condition comes before a narrow one, the wide branch swallows the rows meant for the narrow one.
Order the branches narrowest-first.
from the unit →Why doesn't my CASE match the blank rows?
The short CASE form compares with equals, and a comparison to a missing value is never true, so blank rows fall through to the ELSE.
Use the longer searched form with an explicit is-null test to catch them.
from the unit →That’s the free taste