Conditional aggregation: a column per case

GROUP BY month, status answers the question, but as a long list: one row per month per status. People read reports the other way round, with one row per month and a column for each status. Turning rows into columns like that is called a pivot, and in SQL you build it with a CASE inside an aggregate. The CASE decides which rows each column counts.

SELECT p.category,
       SUM(CASE WHEN o.order_date < '2026-02-01'
                THEN i.quantity * i.unit_price ELSE 0 END) AS jan,
       SUM(CASE WHEN o.order_date >= '2026-02-01' AND o.order_date < '2026-03-01'
                THEN i.quantity * i.unit_price ELSE 0 END) AS feb,
       SUM(CASE WHEN o.order_date >= '2026-03-01'
                THEN i.quantity * i.unit_price ELSE 0 END) AS mar,
       SUM(i.quantity * i.unit_price) AS total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
JOIN products AS p ON p.id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY total DESC;

-- category  jan    feb    mar    total
-- Apparel   228.0  219.0  438.0  885.0
-- Footwear  240.0  108.0  120.0  468.0
-- Gear      75.0   145.0  55.0   275.0

Each row of the join goes through every CASE. A January line adds its value to jan and 0 to the other two, so the three month columns always add up to total. That's a check worth running: 885 + 468 + 275 is 1,628, the paid items total you met in the CTE lesson.

What the CASE gives when it doesn't match matters, and it's different for each aggregate. For SUM, ELSE 0 is harmless. For COUNT, it's a bug: COUNT counts every value that isn't NULL, and 0 isn't NULL, so COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) counts every order. Leave the ELSE out and the CASE gives NULL for the rows you don't want.

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

-- wrong  paid
-- 15     11

AVG has the same trap. The 11 paid orders average 148.0 in items. Add ELSE 0 and the 4 other orders join the average as zeros, which drags it down to 108.53.

Rates come from two aggregates over the same group: 100.0 * COUNT(CASE WHEN status = 'refunded' THEN 1 END) / COUNT(*) is the share of orders refunded. You'll also see two shorter spellings. SUM(status = 'paid') works in SQLite and MySQL, where a comparison is 1 or 0. COUNT(*) FILTER (WHERE status = 'paid') is standard SQL and works in SQLite and PostgreSQL. The CASE form works everywhere, which is why this lesson uses it.

Loading your workspace…