CASE inside an aggregate: counting and summing 'if'
Wrapping CASE inside SUM or COUNT lets you compute several different totals, one per condition, in a single row. It's the SQL version of a pivot table.
GROUP BY normally gives you one summary number per group. But what if you want several summary
numbers side by side, delivered revenue and cancelled revenue, in the same row? Put a CASE
inside the aggregate.
🎯 Explain Like I'm Hired Normally an aggregate adds up everything in the group. A
CASEinside it says "only count this row toward the total if a condition is true," so you can build several separate totals, each with its own condition, all in one row. Example: "delivered revenue and cancelled revenue, side by side, per customer" needs this pattern. Without it, you'd run three separate queries and stitch them back together with joins.
SELECT
customer_id,
SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered_revenue,
SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_revenue
FROM orders
GROUP BY customer_id;
The CASE returns the amount when the condition matches, and 0 otherwise, so each SUM only
adds up the rows it cares about.
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.