Skip to content

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, not 100, 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.