Conditional Aggregation with CASE

## What conditional aggregation is An aggregate function collapses the rows of a group into one value. Applied plainly, it measures *every* row in the group: `COUNT(*)` counts them all, `SUM(amount)` adds them all. That gives one query one answer per aggregate. Conditional aggregation removes that limit by placing a `CASE` expression inside the aggregate's argument, so each row contributes a value chosen by a condition. The aggregate then measures only the subset you care about, while the grouping still ranges over the whole group. ## The counting idiom ```sql SELECT customer_id, SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, COUNT(*) AS total FROM orders GROUP BY customer_id; ``` Read it row by row. For each order, the first `CASE` yields 1 if the status is `'shipped'` and 0 otherwise; `SUM` adds that column of 1s and 0s within the group, so the total equals the number of shipped orders. The second column does the same for cancellations, and `COUNT(*)` gives the group size. One pass over the table produces a small cross-tab: one row per customer, one column per question. ## Why interviewers ask it The naive alternatives are worse. Running three separate `GROUP BY` queries with different `WHERE` clauses and stitching them together in application code costs three round trips and hand-written merge logic. Joining several grouped subqueries together works but is verbose, and any inner side that produces no row for a customer turns into a NULL you have to `COALESCE`. Conditional aggregation expresses all the measures in one `SELECT` list, on one grouping, with no join at all. ## Summing values, not just flags Nothing forces the `THEN` branch to be `1`. Any expression works: ```sql SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped_revenue ``` Other aggregates take a conditional argument too. `MAX(CASE WHEN status = 'shipped' THEN created_at END)` gives the latest shipment date per customer, and `MIN`/`MAX` are the usual choice when the matching subset holds at most one interesting value. `AVG` deserves care, because the `ELSE` branch changes the denominator. `AVG(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)` averages shipped amounts *and zeros for every other row*, which is a different statistic from `AVG(CASE WHEN status = 'shipped' THEN amount END)` — the latter leaves non-matching rows NULL, and aggregates skip NULL input, so it averages shipped orders only. Decide deliberately which of those you want. ## Conditions and three-valued logic The `CASE` condition is an ordinary SQL predicate: it can combine `AND`/`OR`, use `BETWEEN`, `IN`, `IS NULL`, and reference any column of the row — including columns that are not in the `GROUP BY` list, because the expression is evaluated before the rows are collapsed. A `WHEN` branch is taken only when its condition is **TRUE**. If a comparison involves NULL and evaluates to unknown, the branch is *not* taken and control falls through to `ELSE`. So `SUM(CASE WHEN discount > 0 THEN 1 ELSE 0 END)` scores a row with a NULL discount as 0, not as a match; if NULL should mean something else, test for it explicitly with `IS NULL`. What the condition cannot do is reference another row or another aggregate — nesting an aggregate inside the `CASE` is not allowed. ## Practical notes A column alias defined in the `SELECT` list cannot be referenced by a sibling expression in that same list, so if you need to combine two conditional sums (say, into a ratio) you either repeat both expressions or compute them in a derived table or CTE and do the arithmetic one level up. Name the output columns. A wide result of anonymous `SUM(CASE …)` expressions is unreadable; `AS shipped`, `AS cancelled` turn it into a report. And keep the condition list short and stable — the columns are fixed at the time you write the query, which is fine for a handful of known statuses and a poor fit for an open-ended set of values. Finally, note what conditional aggregation preserves: because the condition lives inside the aggregate rather than in `WHERE`, every row still reaches the grouping step, so groups that match nothing still appear with a zero instead of vanishing from the result.