Conditional aggregation & FILTER

Count several things at once

Lesson 5 of 20 · 10 min · Module 2: Aggregation that answers real questions

Managers like one row per customer with a column for every status. Instead of running one query per status, conditional aggregation counts all of them in a single pass.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Count matching rows inside a group
Use FILTER or CASE for each condition
Pivot rows into columns

The idea

SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) counts the rows that match. SQLite also supports the shorter COUNT(*) FILTER (WHERE status = 'shipped'). Each column can use a different condition over the same group.

SELECT COUNT(*) AS all_orders,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

01A condition per column

Every aggregate looks at the same group of rows but only counts those matching its own condition. One scan of the table produces the whole table of numbers.

02CASE works everywhere

SUM(CASE WHEN … THEN 1 ELSE 0 END) is supported by every SQL database. FILTER is clearer and is available in SQLite and PostgreSQL.

SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid
FROM orders;

Output

03Pivoting

Turning the distinct values of a column (statuses) into separate columns is called pivoting. It makes reports easier to read and compare side by side.

Your turn

For each customer who has orders, return name, shipped (orders with status shipped), open (paid or pending) and cancelled. Sort by name.

What the checks look for

Shows shipped, open and cancelled counts per customer

Try it in the editor →

Take this with you

Conditional aggregates turn several queries into one.

Go a little further

Add a column with the share of each customer’s orders that were cancelled, as a percentage.