Conditional aggregation: counting several things at once

Step 1 Understand it

Why you need it. "For each customer, how many orders were delivered and how many weren't?" Running two queries and gluing them together is slow and messy. Interviewers love this pattern because it shows you understand that aggregates work on whatever values you give them.

Watch it happen

Picture it

flowchart LR
  R["Priya's orders<br/>delivered, delivered,<br/>delivered, shipped, delivered"] --> C["CASE gives<br/>1, 1, 1, 0, 1"]
  C --> S["SUM = 4 delivered"]
  R --> T["COUNT(*) = 5 orders"]

The idea

An aggregate adds up whatever expression you hand it. Hand SUM a CASE that gives 1 for the rows you care about and 0 for the rest, and it counts just those rows. Hand it CASE WHEN … THEN amount END and it sums just those amounts. Do this several times in one SELECT, with different conditions, and you get several columns from one pass over the data. Use it with months as the conditions and the rows become columns — that is a pivot. For a percentage, divide one conditional count by COUNT(*), using 100.0 to avoid integer division.

The shape of it

SELECT group_column,
       SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS how_many,
       SUM(CASE WHEN condition THEN value END)    AS how_much
FROM table_name
GROUP BY group_column;

Words in lower case are the parts you fill in.

How it works, step by step

1. GROUP BY customer_id makes one group per customer.
2. For each row, the CASE gives 1 if the order is delivered and 0 if not.
3. SUM adds those 1s and 0s, which is the number of delivered orders in the group.
4. A second SUM with the opposite condition counts the rest. COUNT(*) gives the total.
5. For a pivot, each column's CASE picks one month: CASE WHEN order_date LIKE '2024-01%' THEN amount ELSE 0 END.

Analogy

Counting a class with tally marks on a sheet with several columns: as each pupil walks past, you add a mark under "walked", "cycled" or "came by bus". One walk-past fills in every column at once.

Where the analogy breaks

With tally marks a pupil can only go in one column. Conditional aggregation has no such rule: one row can count towards several columns if several conditions are true, so make your conditions not overlap when they should add up to the total.

Worked examples

SELECT customer_id,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END) AS not_delivered
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

SELECT customer_id,
       SUM(CASE WHEN order_date LIKE '2024-01%' THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN order_date LIKE '2024-02%' THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN order_date LIKE '2024-03%' THEN amount ELSE 0 END) AS mar
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered,
       AVG(CASE WHEN status = 'delivered' THEN 1.0 ELSE 0 END) AS share_delivered
FROM orders;

Must-know points

• SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts matching rows; COUNT(CASE WHEN cond THEN 1 END) does the same, because COUNT skips the NULLs.
• SUM(CASE WHEN cond THEN amount END) sums only matching amounts.
• One pass over the data gives many totals — faster and simpler than several queries.
• A pivot is conditional aggregation with one column per value.
• For percentages, multiply by 100.0 before dividing.

Common mistakes

• Writing COUNT(CASE WHEN cond THEN 1 ELSE 0 END). COUNT counts the 0s too, because 0 is not NULL. Use SUM, or drop the ELSE.
• Computing a percentage with whole numbers: 100 * 7 / 10 is fine but 7 / 10 * 100 is 0.
• Conditions that overlap when the columns should add up to the total.

In other databases

• PostgreSQL and SQLite support a FILTER clause: COUNT(*) FILTER (WHERE status = 'delivered'). It is the same idea with neater syntax.
• SQL Server and Oracle have a PIVOT operator, but CASE works everywhere and interviewers accept it.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  Mode SQL Tutorial — SQL CASE (Using CASE with aggregate functions)

  Mode, free

• Read

  PostgreSQL docs — Aggregate Expressions (FILTER)

  official documentation, free

• Practice

  DataLemur — conditional aggregation questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain how SUM(CASE WHEN … THEN 1 ELSE 0 END) counts rows.

   Show answer

   The CASE runs once for every row and turns it into a number: 1 if the row matches, 0 if it doesn't. SUM then adds up those 1s and 0s. Every match adds 1 and every other row adds nothing, so the total is the number of matching rows.

   Because each CASE is its own column, one query can count several different things at once, each with its own condition.

2. Writefrom a blank page, then run it

   For each department, show the number of employees earning 60000 or more and the number earning less, in one query.

   Show answer

   Two SUM(CASE …) columns, one per condition, grouped by department. "60000 or more" is >= 60000, so Dara (exactly 60,000) counts in the first column.

   sqlCopy

   SELECT dept_id,
          SUM(CASE WHEN salary >= 60000 THEN 1 ELSE 0 END) AS earn_60k_or_more,
          SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END) AS earn_less
   FROM employees
   GROUP BY dept_id
   ORDER BY dept_id;

   Try it

   4 rows

   dept_id	earn_60k_or_more	earn_less
   NULL	0	1
   1	4	0
   2	1	1
   3	0	1

3. Predictsay it before you run it

   What does SELECT COUNT(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) FROM orders; return, and why is it not 7?

   Show answer

   10, not 7.

   COUNT counts every value that is not NULL. The CASE gives 1 or 0, and 0 is a real value, not NULL, so COUNT counts all 10 rows.

   Two fixes: use SUM, which adds the 1s and 0s; or keep COUNT but drop the ELSE, so non-matching rows become NULL and are skipped. Both give 7.

   sqlCopy

   SELECT COUNT(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS counted,
          COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS fixed_count,
          SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS fixed_sum
   FROM orders;

   Try it

   1 row

   counted	fixed_count	fixed_sum
   10	7	7

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Write the share of orders that are cancelled, as a percentage with one decimal place, and check it is 10.0.

   Show answer

   1 cancelled order out of 10 is 10.0%. Start with 100.0, not 100, so the division is exact. With whole numbers the fraction is cut off: 1 out of 3 would show 33, not 33.3. (SQLite shows 10.0 as 10.)

   sqlCopy

   SELECT ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_cancelled
   FROM orders;

   Try it

   1 row

   pct_cancelled
   10

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

Conditional aggregation puts a CASE expression inside an aggregate, like SUM of CASE WHEN status is delivered THEN 1 ELSE 0, so I can compute several metrics per group in a single pass — counts by status, revenue by month as columns, or a ratio. For ratios I multiply by 100.0 first to avoid integer division, and I make sure SUM rather than COUNT wraps a CASE that returns zero.

Follow-up questions

Tick each check when you can do it without notes