Conditional aggregation and string aggregation

Why conditional aggregation?

GROUP BY status gives one row per status, going down the page. Managers often want the same numbers across the page instead, as columns: Placed 2, Shipped 1, Delivered 4, Cancelled 1, all in one row. Turning row values into columns like this is called a pivot, and in plain SQL it is done with a CASE inside an aggregate.

SUM(CASE WHEN ...)

CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END gives 1 for delivered rows and 0 for the rest. SUM of those ones and zeros is the number of delivered rows. One such SUM per status makes one column per status.

SELECT SUM(CASE WHEN status = 'Placed'    THEN 1 ELSE 0 END) AS placed,
       SUM(CASE WHEN status = 'Shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;
-- 2 | 1 | 4 | 1

Add GROUP BY and you get a pivot per group. Here, for each category, how many products are in stock and how many are sold out:

SELECT category,
       SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock,
       SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS out_of_stock
FROM products
GROUP BY category;
-- Electronics | 1 | 1
-- Fashion     | 2 | 0
-- Grocery     | 2 | 0
-- Kitchen     | 2 | 0

Two other forms do the same job. COUNT(CASE WHEN cond THEN 1 END) works because CASE gives NULL when nothing matches and COUNT skips NULLs. And SUM(CASE WHEN cond THEN amount ELSE 0 END) adds up an amount for the matching rows only, for example delivered revenue next to total revenue.

FILTER: a cleaner way

SELECT city,
       COUNT(*) AS students,
       COUNT(*) FILTER (WHERE fees_paid = 1) AS paid
FROM students
GROUP BY city;
-- Chennai | 1 | 1
-- Delhi   | 2 | 1
-- Pune    | 3 | 2   ... 5 rows

Percentages

A percentage is part × 100.0 / whole, worked out per group. Multiply by 100.0 first so integer division does not cut it to 0 (module 3), then round.

SELECT city, ROUND(100.0 * SUM(fees_paid) / COUNT(*), 1) AS paid_pct
FROM students GROUP BY city;
-- Delhi | 50
-- Pune  | 66.7   ... (fees_paid is 1 or 0, so SUM counts the payers)

Joining text inside a group

Sometimes you want the members of a group listed in one cell: the products in each category, the team in each department. GROUP_CONCAT(x, separator) in SQLite and MySQL joins the values of a group into one text. The default separator is a comma.

SELECT category, GROUP_CONCAT(name, ', ' ORDER BY name) AS products
FROM products
GROUP BY category;
-- Electronics | Bluetooth Earbuds, Power Bank 10000mAh
-- Kitchen     | Pressure Cooker 3L, Steel Tiffin Box   ... 4 rows

Common mistakes

• Forgetting ELSE 0 in SUM(CASE ...): a group with no matches then shows NULL instead of 0. Use ELSE 0, or COUNT(CASE ... THEN 1 END), which gives 0.
• Writing COUNT(CASE WHEN cond THEN 1 ELSE 0 END): COUNT counts the zeros too, so you get the row count. With COUNT, leave out the ELSE.
• Integer percentages: SUM(x) * 100 / COUNT(*) drops the fraction. Use 100.0.
• Relying on the order inside GROUP_CONCAT without ORDER BY. Add ORDER BY inside the aggregate.
• Hard-coding a pivot column for each status and forgetting a new status added later. Check with a plain GROUP BY status that the columns add up to the total.