Aggregation & GROUP BY · Medium
How do you pivot rows into columns using conditional aggregation?
Short answer
Wrap a CASE expression inside an aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matching rows, and SUM(CASE WHEN condition THEN value END) totals them. One GROUP BY then produces several metrics side by side. It works in every SQL dialect, unlike PIVOT syntax, and avoids running separate queries for each category.
Detailed answer
Task: one row per city with the count of orders in each status and a cancellation rate.
SELECT c.city,
COUNT(*) AS total_orders,
SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) AS returned,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
ROUND(100.0 * SUM(CASE WHEN o.status IN ('cancelled','returned') THEN 1 ELSE 0 END)
/ COUNT(*), 1) AS pct_cancel_or_return
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY total_orders DESC;
Notes:
- Multiply by
100.0, not100, so that integer division does not truncate the rate to zero (SQLite and PostgreSQL both do integer division on integers). - PostgreSQL also supports
COUNT(*) FILTER (WHERE status = 'returned'), which reads more cleanly; the CASE form is the portable answer. - The same technique builds a "this year vs last year" side-by-side report — see the YoY question.