Conditional aggregation
Finance wants a monthly order health report: “Can we get one row per month, with a column per status — how many completed, how many pending, how many refunded, how many cancelled? I want to paste this straight into a spreadsheet.”
One row per group, a count per something — that's GROUP BY. Group by month and status, COUNT(*).
Try it, then count the rows finance actually gets per month.
SELECT SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month, status, COUNT(*) AS n
FROM orders
GROUP BY SUBSTR(CAST(placed_at AS TEXT), 1, 7), status
ORDER BY month, status;Run that and January 2023 comes back as two rows — one for completed, one for pending. June comes back as two rows too — cancelled and refunded. Most months come back as one row. Finance wanted one row per month, every time, with the statuses sitting side by side as columns. This gives a different number of rows per month depending on which statuses happened to occur — useless for pasting into a spreadsheet where every row needs the same shape.
The problem is what’s in the GROUP BY. Grouping by both month and status is exactly what splits each month across multiple rows. To keep one row per month, status has to come out of GROUP BY entirely — and move inside the aggregate instead, as a condition on what gets counted.
That’s what CASE WHEN is for here: CASE WHEN status = ’completed’ THEN 1 END returns 1 when the condition holds and NULL otherwise — and COUNT already knows to ignore NULLs. So COUNT(CASE WHEN status = ’completed’ THEN 1 END) quietly means “count only the completed ones,” while every row still belongs to the same month group.
One group per row you want out. One CASE WHEN per column you want to split.
SELECT
SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded_count,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders
GROUP BY SUBSTR(CAST(placed_at AS TEXT), 1, 7)
ORDER BY month;| month | completed_count | pending_count | refunded_count | cancelled_count |
|---|---|---|---|---|
| 2022-12 | 1 | 0 | 0 | 0 |
| 2023-01 | 1 | 1 | 0 | 0 |
| 2023-02 | 2 | 0 | 0 | 0 |
| 2023-03 | 3 | 0 | 0 | 0 |
| 2023-04 | 1 | 0 | 0 | 0 |
| 2023-05 | 4 | 0 | 0 | 0 |
| 2023-06 | 0 | 0 | 1 | 1 |
| 2023-07 | 1 | 0 | 0 | 0 |
| 2023-08 | 4 | 0 | 0 | 0 |
| 2023-09 | 1 | 1 | 0 | 0 |
One row per month, every time, four status columns sitting side by side. And now a pattern jumps out that the previous query buried: June 2023 has zero completed orders — just one cancellation and one refund. That’s the kind of month finance actually wanted to be able to spot at a glance.
The same trick works with SUM in place of COUNT, if what you want is revenue per status instead of order counts:
SELECT
SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month,
SUM(CASE WHEN status = 'completed' THEN amount END) AS completed_revenue,
SUM(CASE WHEN status = 'refunded' THEN amount END) AS refunded_amount
FROM orders
GROUP BY SUBSTR(CAST(placed_at AS TEXT), 1, 7)
ORDER BY month;Running…
Try it: What if you switch COUNT for AVG — does CASE WHEN without an ELSE still behave the same way?
Run your query to see results here.
No — and this is worth being careful about. January 2023 has one completed order ($34.50) and one pending one. Without an ELSE, the pending row’s CASE evaluates to NULL, and AVG ignores NULLs entirely — so you get the average of just the completed orders, $34.50. Add ELSE 0 and the pending row now counts as a real 0 in the average, dragging it down to $17.25.COUNT and SUM don’t care either way, because a missing value and a zero contribute the same amount to a count or a sum — nothing. An average is different: it divides by how many values it saw, and ELSE 0 changes that number.
Ops wants one row per product category, with two columns: how many orders in that category were completed, and how many were anything else (pending, refunded, or cancelled).
Hint 1
Same shape as the monthly report — one row per group, with the status split happening inside the aggregate instead of in GROUP BY.
Hint 2
Join products to orders, GROUP BY category, and use COUNT(CASE WHEN ... THEN 1 END) for the completed column.
Hint 3
For 'anything else,' you don't need to list every other status — a condition like status <> 'completed' covers all of them at once.
Solution
Running…
Audio is the only category with a perfect record — five orders, all completed. Every other category has exactly one order that didn’t go through cleanly. Same trick as the monthly report: one GROUP BY key (category, this time, not month), and every column beyond it built with its own CASE WHEN inside the aggregate.
To split one aggregate into several side-by-side columns, keep the grouping key narrow (just what you want one row per) and move the splitting condition inside the aggregate with CASE WHEN. COUNT and SUM already ignore the NULL a non-matching CASE produces, so you rarely need an ELSE — except with AVG, where ELSE 0 changes how many rows count toward the denominator.