How do SUM(CASE WHEN c THEN 1 ELSE 0 END) and COUNT(CASE WHEN c THEN 1 END) differ?
answer
- COUNT ignores NULL arguments
- a CASE with no ELSE yields NULL
- ELSE 0 is safe under one of them only
- zero is a value, not a missing value
- empty match: 0 versus NULL
basics
~20 sBoth return the number of rows where the condition is true. COUNT relies on the missing ELSE producing NULL for non-matching rows, which COUNT ignores. Adding ELSE 0 to the COUNT form breaks it: 0 is not NULL, so it counts every row.
solid answer
~40 sThey are equivalent counts, arrived at differently. `SUM(CASE WHEN c THEN 1 ELSE 0 END)` adds 1 per matching row and 0 per non-matching row. `COUNT(CASE WHEN c THEN 1 END)` has no `ELSE`, so a non-matching row yields NULL, and `COUNT(expr)` skips NULL arguments — leaving the matching rows counted. The trap is `COUNT(CASE WHEN c THEN 1 ELSE 0 END)`: zero is a perfectly good non-NULL value, so it counts *every* row in the group and silently reports the group size. The other difference is the empty-match case: with `ELSE 0` (or with `COUNT`) a group where nothing matches returns 0, whereas `SUM(CASE WHEN c THEN 1 END)` sums an all-NULL input and returns NULL, so wrap it in `COALESCE(…, 0)` if a numeric zero is required.
code
sql · 5 lines-- 10 rows in the group, 3 of them cancelled
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) -- 3 correct
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) -- 3 correct
COUNT(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) -- 10 BUG: 0 is not NULL
SUM(CASE WHEN status = 'cancelled' THEN 1 END) -- 3, but NULL if none matchgo deeper
Learn both shapes and the one rule that separates them: COUNT skips NULL arguments, so the COUNT form must have no ELSE branch.
Explain the mechanics precisely, including why COUNT(CASE … ELSE 0 END) silently degrades into COUNT(*) and why a SUM without ELSE can return NULL.
Show that you catch this in review: the bug produces a plausible number, never an error, so name the symptom (a conditional count exactly equal to the group size) and the COALESCE guard for NULL sums.
Own the convention. Decide which spelling the team uses for conditional counts and why, so that reviewers do not have to re-derive NULL behavior column by column in wide reporting queries.
## Two spellings of the same measure Conditional counting has two idiomatic forms, and interviewers use the pair to test whether a candidate really knows how aggregates treat NULL. ```sql SELECT customer_id, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_sum, COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count FROM orders GROUP BY customer_id; ``` Both columns return the number of cancelled orders per customer. They get there by different routes. ## How each one works `SUM(CASE … THEN 1 ELSE 0 END)` maps every row to a number: 1 when the predicate is true, 0 otherwise. Summing that column of ones and zeros yields the count of matches. Nothing depends on NULL handling; the arithmetic does all the work. `COUNT(CASE … THEN 1 END)` relies on two rules working together. First, a `CASE` with no `ELSE` branch has an implicit `ELSE NULL`, so a non-matching row evaluates to NULL. Second, `COUNT(expr)` counts the rows where *expr* is not NULL. The NULLs are therefore invisible to the count, and only matching rows are tallied. The value in the `THEN` branch is irrelevant — `THEN 1`, `THEN 'x'`, `THEN order_id` all count the same, because `COUNT` only asks "is this NULL?". ## The classic bug ```sql -- WRONG: counts every row in the group COUNT(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) ``` Habit makes people write `ELSE 0` on every conditional aggregate. Under `SUM` that is correct; under `COUNT` it destroys the expression. Zero is a non-NULL value, so every row now supplies a countable argument and the result equals `COUNT(*)` — the group size, reported confidently as a cancellation count. It never errors and it never looks obviously wrong, which is exactly why it is asked. The rule to carry: **`ELSE 0` belongs with `SUM`; `COUNT` wants the branch to be missing.** ## The empty-match difference The forms genuinely diverge when a group contains rows but none of them match. - `SUM(CASE WHEN c THEN 1 ELSE 0 END)` → 0. Every row contributed 0. - `COUNT(CASE WHEN c THEN 1 END)` → 0. `COUNT` of nothing is 0 by definition. - `SUM(CASE WHEN c THEN 1 END)` → **NULL**. Every row contributed NULL, aggregates skip NULL input, and a `SUM` with no surviving input is NULL, not 0. That last line is the other half of the question. Dropping `ELSE 0` from a `SUM` is not a harmless simplification: downstream arithmetic then propagates NULL (`NULL + 5` is NULL), a `NOT NULL` target column rejects the insert, and a report shows a blank cell where a zero belongs. Either keep `ELSE 0` or write `COALESCE(SUM(CASE WHEN c THEN 1 END), 0)`. ## Unknown conditions fall to ELSE A `WHEN` branch is taken only when its predicate evaluates to TRUE. If the predicate involves a NULL and evaluates to unknown, the branch is skipped and control reaches `ELSE` — explicit or implicit. So `SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END)` scores a row whose `amount` is NULL as 0, and `COUNT(CASE WHEN amount > 100 THEN 1 END)` does not count it. Both forms agree here, and both differ from what someone expecting "NULL is not > 100, therefore false" *hopes* — the outcome happens to match, but the reason is the unknown-is-not-true rule, and it stops matching as soon as you write `NOT (amount > 100)`, which is also unknown and also fails. ## Choosing between them Both are portable and standard. Preferences worth defending: - Use `SUM(CASE … ELSE 0 END)` when the `THEN` branch is a real measure (`THEN amount`), when you want a guaranteed non-NULL zero, or when the column sits in arithmetic with other columns. - Use `COUNT(CASE … THEN 1 END)` when you are strictly counting rows; it reads as "count the ones that match", and there is no `ELSE` to get wrong. - Be consistent within a query. Mixing the two spellings in one wide `SELECT` list is a review comment waiting to happen, because a reader must re-derive each column's NULL behavior. Whichever you pick, alias the column. `cancelled_orders` tells the next reader what the number is; a bare `SUM(CASE …)` header does not.
- Why does SUM(CASE WHEN c THEN 1 END) return NULL for a group where nothing matches?Every row evaluates to NULL because the implicit `ELSE NULL` fires. Aggregates skip NULL inputs, so `SUM` is left with no values at all, and a sum over no values is NULL rather than 0. Use `ELSE 0` or `COALESCE(…, 0)` when a numeric zero matters downstream.
- Does the value in the THEN branch matter for the COUNT form?No. `COUNT(expr)` only checks whether the argument is NULL, so `THEN 1`, `THEN order_id` and `THEN 'x'` all produce the same count. Writing `THEN 1` is convention, not requirement. It matters enormously for the `SUM` form, where the value is what gets added.
- How would you count rows where a nullable column is NULL?Test it explicitly: `COUNT(CASE WHEN shipped_at IS NULL THEN 1 END)`. A comparison such as `shipped_at = NULL` evaluates to unknown, the `WHEN` branch never fires, and the count comes back 0 for every group.
saying these in an interview costs you the question
- Writes ELSE 0 inside COUNT and expects a filtered count
- Thinks COUNT(expr) counts NULLs as rows
- Says SUM without ELSE always returns 0 when nothing matches
- Believes the THEN value changes the COUNT result
- Assumes a row with a NULL column takes the THEN branch