Conditional Aggregation

Combining aggregates with CASE — or the standard FILTER clause — lets one query calculate many different totals at once. It's how you build pivot-style reports, where categories become columns, in a single pass over the data.

Counting by condition

SQL

SELECT COUNT(*) AS total,
       COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
       COUNT(CASE WHEN status IN ('pending', 'shipped') THEN 1 END) AS open,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders;

Result 1 row

total	delivered	open	cancelled
15	9	5	1

A CASE without ELSE returns NULL for non-matching rows, and COUNT ignores NULLs — so each column counts only its own rows.

The FILTER clause

SQL

SELECT COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
       COUNT(*) FILTER (WHERE status IN ('pending', 'shipped')) AS open
FROM orders;

Result 1 row

total	delivered	open
15	9	5

Standard SQL's FILTER (WHERE …) says the same thing more directly. It's supported by PostgreSQL and SQLite; use the CASE form for MySQL, SQL Server and Oracle.

Pivoting: rows into columns

SQL

SELECT strftime('%Y-%m', order_date) AS month,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY month
ORDER BY month;

Result 7 rows

month	delivered	shipped	pending	cancelled
2025-09	2	0	0	0
2025-10	2	0	0	0
2025-11	1	0	0	1
2025-12	2	0	0	0
2026-01	1	1	0	0
2026-02	0	1	2	0
2026-03	1	0	1	0

Grouping by month and spreading statuses across columns gives a compact, spreadsheet-like report. SQL Server and Oracle also have a PIVOT operator, but this CASE technique works everywhere.

Conditional sums

SQL

SELECT p.category,
       SUM(oi.quantity) AS units,
       round(SUM(CASE WHEN o.order_date < '2026-01-01' THEN oi.quantity * oi.unit_price ELSE 0 END), 2) AS revenue_2025,
       round(SUM(CASE WHEN o.order_date >= '2026-01-01' THEN oi.quantity * oi.unit_price ELSE 0 END), 2) AS revenue_2026
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY p.category;

Result 4 rows

category	units	revenue_2025	revenue_2026
Books	8	102.4	147.3
Electronics	9	208.96	199.96
Home	12	124	178.5
Toys	4	75.9	18

Percentages of a total

SQL

SELECT round(100.0 * COUNT(*) FILTER (WHERE status = 'delivered') / COUNT(*), 1) AS pct_delivered,
       round(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*), 1) AS pct_cancelled
FROM orders;

Result 1 row

pct_delivered	pct_cancelled
60	6.7

Multiplying by 100.0 first avoids integer division (lesson 13).

Averages of a subset

SQL

SELECT round(AVG(salary), 0) AS avg_all,
       round(AVG(CASE WHEN hire_date >= '2022-01-01' THEN salary END), 0) AS avg_recent_hires,
       round(AVG(CASE WHEN hire_date < '2022-01-01' THEN salary END), 0) AS avg_earlier_hires
FROM employees;

Result 1 row

avg_all	avg_recent_hires	avg_earlier_hires
73167	56500	81500

For averages, leave out the ELSE: ELSE 0 would add zeros and drag the average down.

Try it yourself

For each country, count customers who signed up before 2025 and from 2025 onwards.

PreviousHAVING NextKeys & Relationships