WHERE and GROUP BY answer one question per query: how many paid orders per month? Reports usually want several answers side by side — paid, pending and refunded orders per month, in three columns, with a zero where there were none. Running three queries and gluing the results together is slow and clumsy. Conditional aggregation does it in one pass: put the condition inside the aggregate. It is the technique behind pivot tables, rates and percentages, and a large family of interview questions (monthly transaction reports, approval rates, "count by status").

The idea: an aggregate over a CASE

An aggregate runs over the values of an expression. Make the expression 1 when a row qualifies and 0 when it does not, and SUM counts the qualifying rows:

SELECT DATE_FORMAT(created_on, '%Y-%m') AS month,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'paid'     THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending'  THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders
GROUP BY DATE_FORMAT(created_on, '%Y-%m')
ORDER BY month;

Every row of every month goes through all three CASEs; each row adds 1 to exactly one column. The month with no pending orders shows 0 — it did not disappear, which is what a WHERE status = 'pending' would have done to it.

Three spellings of a conditional count

SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)   -- standard, everywhere
COUNT(CASE WHEN status = 'paid' THEN 1 END)        -- standard: no ELSE → NULL → not counted
SUM(status = 'paid')                               -- MySQL only: a comparison is 1 or 0

The COUNT form relies on CASE returning NULL when no branch matches and COUNT(expr) skipping NULLs. Writing ELSE 0 there would be a bug — COUNT counts zeros. The MySQL shortcut is compact and common in MySQL code, but PostgreSQL rejects SUM of a boolean and SQL Server has no boolean expressions in that position, so use CASE in anything meant to travel. Note one difference: if status is NULL, SUM(status = 'paid') adds NULL (skipped), while the CASE form adds 0 — the totals are the same.

Conditional sums of amounts

Return the amount instead of 1 and you get conditional totals:

SELECT customer_id,
       SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid_amount,
       SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
     - SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS net
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Use ELSE 0 here rather than leaving it out: without it a customer with no refunds gets NULL for refunded_amount, and net becomes NULL too, because any arithmetic with NULL is NULL.

Pivoting rows into columns

Turning the values of one column into separate output columns is called a pivot. Quarterly sales stored one row per sale become one row per product with a column per quarter:

SELECT product,
       SUM(CASE WHEN QUARTER(sold_on) = 1 THEN amount ELSE 0 END) AS q1,
       SUM(CASE WHEN QUARTER(sold_on) = 2 THEN amount ELSE 0 END) AS q2,
       SUM(CASE WHEN QUARTER(sold_on) = 3 THEN amount ELSE 0 END) AS q3,
       SUM(CASE WHEN QUARTER(sold_on) = 4 THEN amount ELSE 0 END) AS q4
FROM sales
WHERE sold_on >= '2024-01-01' AND sold_on < '2025-01-01'
GROUP BY product
ORDER BY product;

The rows of the output are the GROUP BY; the columns are the CASEs. MySQL has no PIVOT keyword (SQL Server does, and PostgreSQL has crosstab in an extension), so this pattern is how MySQL pivots — and it works on every database. Its limitation is that the columns must be known when the query is written; a pivot over values that change needs the query built by application code.

MAX(CASE WHEN … THEN value END) is the pivot form for non-numeric values — for example turning key/value rows (attribute = 'color', value = 'red') into one row per item with a column per attribute. Each group has at most one matching row, and MAX just picks it out of the NULLs.

Rates and percentages

A rate is a conditional count divided by a count:

SELECT country,
       COUNT(*) AS trans_count,
       SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END)  AS approved_count,
       ROUND(100 * SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) / COUNT(*), 1) AS approval_pct
FROM transactions
GROUP BY country
ORDER BY country;

AVG does the division for you: the average of a column of 1s and 0s is the fraction of 1s.

ROUND(100 * AVG(CASE WHEN state = 'approved' THEN 1 ELSE 0 END), 1) AS approval_pct
ROUND(AVG(rating >= 4), 2)                                          -- MySQL: share of good ratings

Two cautions. In PostgreSQL and SQL Server integer division turns SUM(…) / COUNT(*) into 0 — write 100.0 *. And AVG(CASE WHEN … THEN 1 END) without ELSE 0 averages only the 1s and always returns 1; the ELSE 0 is what puts the non-matching rows in the denominator.

Conditional MIN, MAX and DISTINCT counts

Any aggregate takes a CASE:

SELECT customer_id,
       MIN(CASE WHEN status = 'paid' THEN created_on END)            AS first_paid,
       MAX(CASE WHEN status = 'refunded' THEN created_on END)        AS last_refund,
       COUNT(DISTINCT CASE WHEN status = 'paid' THEN product_id END) AS products_bought
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Here the absence of ELSE is deliberate: non-matching rows give NULL, and MIN, MAX and COUNT(DISTINCT …) skip NULLs. A customer with no refunds gets NULL for last_refund, which is the honest answer.

Filtering on conditional aggregates

Conditional aggregates work in HAVING like any other:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) = 0     -- never refunded
   AND COUNT(*) >= 2
ORDER BY customer_id;

"Customers who bought A but never B" is the same shape: HAVING SUM(CASE WHEN product = 'A' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN product = 'B' THEN 1 ELSE 0 END) = 0.

Standard SQL also defines a FILTER clause — COUNT(*) FILTER (WHERE status = 'paid') — that PostgreSQL and SQLite support. MySQL does not, so in MySQL the CASE form is the way.

Pitfalls

• COUNT(CASE WHEN … THEN 1 ELSE 0 END) counts every row — drop the ELSE or use SUM.
• AVG(CASE WHEN … THEN 1 END) without ELSE 0 is always 1.
• Conditional sums without ELSE 0 turning totals into NULL.
• Integer division in rates outside MySQL.
• Using WHERE for one condition when every group should stay.

Key takeaways

• SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts matching rows inside each group.
• Several conditional aggregates side by side pivot rows into columns.
• AVG over a 1/0 CASE is a rate; multiply by 100 for a percentage.
• MIN/MAX/COUNT(DISTINCT) over a CASE with no ELSE pick from the matching rows only.
• MySQL's SUM(cond) shortcut is not portable; CASE is.