Conditional Aggregation: CASE Inside SUM and COUNT

CASE picks a value. It tests its WHEN branches from top to bottom and returns the value of the first one that is true. With no match it returns the ELSE value, or NULL if you wrote none. It can sit anywhere a value can: in SELECT, in WHERE, inside an aggregate.

SELECT name,
       CASE WHEN age >= 65 THEN 'senior'
            WHEN age >= 18 THEN 'adult'
            ELSE 'minor' END AS band
FROM people;

Order matters. Branches are tried in the order written, so list the narrowest or newest range first. age >= 18 before age >= 65 would call everyone over 18 an adult and never reach the senior branch.

Conditional aggregation puts the CASE inside the aggregate. Each aggregate sees every row but only counts the ones its CASE lets through, so one pass over the data fills several columns. This is the standard way to pivot rows into columns in SQL:

SELECT product,
       SUM(CASE WHEN strftime('%m', sold_on) = '01' THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN strftime('%m', sold_on) = '02' THEN amount ELSE 0 END) AS feb
FROM sales
GROUP BY product;

Write ELSE 0 inside a SUM. Without it, a month with no rows adds NULL, and a SUM of only NULLs is NULL rather than 0, which shows up as a blank in a report.

COUNT skips NULL. COUNT(CASE WHEN status = 'paid' THEN 1 END) counts only the paid rows, because the CASE gives NULL for the rest and COUNT ignores NULL. The same pattern gives several counters in one query:

SELECT region,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid,
       COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders
GROUP BY region;

The NULL toolbox. COALESCE(x, 'direct') returns the first non-NULL argument, so it swaps an unknown for a label. NULLIF(x, 0) returns NULL when x is 0, which turns a divide-by-zero into a harmless NULL: dividing by NULL gives NULL.

SELECT region,
       COALESCE(manager, 'unassigned') AS manager,
       100.0 * SUM(refunds) / NULLIF(SUM(orders), 0) AS refund_pct
FROM regions
GROUP BY region;

Percent of total. Put the whole in the same row as the part: a scalar subquery with the same filters, or a window SUM(...) OVER (). Multiply by 100.0, not 100, so the division is not integer division, then ROUND.

SELECT product,
       ROUND(100.0 * SUM(amount) / (SELECT SUM(amount) FROM sales), 1) AS pct
FROM sales
GROUP BY product;