Conditional aggregation: counting several things at once
Conditional aggregation puts CASE inside an aggregate, such as SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END), so that it counts or sums only the rows that match. One query can then produce several different totals, or turn rows into columns (a pivot).
Why it matters
"For each customer, how many orders were delivered and how many weren't?" Running two queries and gluing them together is slow and messy. Interviewers love this pattern because it shows you understand that aggregates work on whatever values you give them.
The idea
An aggregate adds up whatever expression you hand it. Hand SUM a CASE that gives 1 for the rows you care about and 0 for the rest, and it counts just those rows. Hand it CASE WHEN … THEN amount END and it sums just those amounts.
Do this several times in one SELECT, with different conditions, and you get several columns from one pass over the data. Use it with months as the conditions and the rows become columns — that is a pivot. For a percentage, divide one conditional count by COUNT(*), using 100.0 to avoid integer division.
Counting a class with tally marks on a sheet with several columns: as each pupil walks past, you add a mark under "walked", "cycled" or "came by bus". One walk-past fills in every column at once.
With tally marks a pupil can only go in one column. Conditional aggregation has no such rule: one row can count towards several columns if several conditions are true, so make your conditions not overlap when they should add up to the total.
Syntax
SELECT group_column,
SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS how_many,
SUM(CASE WHEN condition THEN value END) AS how_much
FROM table_name
GROUP BY group_column;How it works
- GROUP BY customer_id makes one group per customer.
- For each row, the CASE gives 1 if the order is delivered and 0 if not.
- SUM adds those 1s and 0s, which is the number of delivered orders in the group.
- A second SUM with the opposite condition counts the rest.
COUNT(*)gives the total. - For a pivot, each column's CASE picks one month:
CASE WHEN order_date LIKE '2024-01%' THEN amount ELSE 0 END.
Key points
SUM(CASE WHEN cond THEN 1 ELSE 0 END)counts matching rows;COUNT(CASE WHEN cond THEN 1 END)does the same, because COUNT skips the NULLs.SUM(CASE WHEN cond THEN amount END)sums only matching amounts.- One pass over the data gives many totals — faster and simpler than several queries.
- A pivot is conditional aggregation with one column per value.
- For percentages, multiply by 100.0 before dividing.
Common mistakes
- Writing
COUNT(CASE WHEN cond THEN 1 ELSE 0 END). COUNT counts the 0s too, because 0 is not NULL. Use SUM, or drop the ELSE. - Computing a percentage with whole numbers:
100 * 7 / 10is fine but7 / 10 * 100is 0. - Conditions that overlap when the columns should add up to the total.
Not true: COUNT(CASE WHEN cond THEN 1 ELSE 0 END) counts only the rows that match the condition.
In fact: COUNT counts every value that is not NULL, and 0 is not NULL, so it counts every row. Use SUM instead, or leave out the ELSE.
In the interview
Conditional aggregation puts a CASE expression inside an aggregate, like SUM of CASE WHEN status is delivered THEN 1 ELSE 0. That lets me compute several metrics per group in a single pass: counts by status, revenue by month as columns, or a ratio. For ratios I multiply by 100.0 first to avoid integer division, and I make sure SUM rather than COUNT wraps a CASE that returns zero.
Part of Unit 2: Summarising data: aggregates, GROUP BY and HAVING in the free SQL course.