Conditional aggregation

Part of the free LogicWiz SQL & Databases course, Module 4: Aggregates and GROUP BY. The queries and practice run in the app on real Postgres.

Meher wants one table: each restaurant on a row, with delivered, cancelled and pending counts side by side. GROUP BY restaurant_id, status gives the numbers, but stacked in 13 rows. How do you turn them sideways?

You'll learn to

• Count or add only some rows inside a group with SUM(CASE …) and FILTER
• Build report-style pivots: one row per thing, one column per category
• Work out percentages like a cancellation rate without the integer trap

Learn

An aggregate over a CASE

Put a CASE inside the aggregate and it decides, row by row, what gets counted:

SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)

Every cancelled row adds 1, every other row adds 0, so the sum is the number of cancelled rows. Write one such column per status and you get all the counts in one row per group, in one pass over the table.

A status pivot per restaurant

SELECT restaurant_id,
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders
GROUP BY restaurant_id
ORDER BY restaurant_id;

This is called a pivot: values that were in rows (status) become columns. It is the shape managers want in a report.

The Postgres way: FILTER

SELECT restaurant_id,
  COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(delivery_fee) FILTER (WHERE status = 'delivered') AS fees_earned
FROM orders
GROUP BY restaurant_id
ORDER BY restaurant_id;

agg(…) FILTER (WHERE condition) runs the aggregate only on the rows that pass. It reads like English. Note: SUM … FILTER on no matching rows is NULL, while COUNT is 0.

COUNT(CASE WHEN … THEN 1 ELSE 0 END) counts every row, because COUNT counts anything that is not NULL, and 0 is not NULL. With COUNT, leave out the ELSE (so non-matches become NULL): COUNT(CASE WHEN … THEN 1 END). With SUM, use ELSE 0.

A rate in one query

SELECT restaurant_id,
  COUNT(*) AS orders,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*), 1) AS cancel_pct
FROM orders
GROUP BY restaurant_id
ORDER BY cancel_pct DESC, restaurant_id;

Start with 100.0 so the division is decimal. Without it, 1 cancelled out of 5 gives 1 / 5 = 0, and every rate looks perfect.

Three ways to count matching rows

MySQL has no FILTER. Use SUM(CASE …), or MySQL's shortcut SUM(status = 'cancelled'), which works because a true comparison is 1 in MySQL. In an interview, write the CASE version: it runs everywhere.

Pivot questions are interview regulars: "show male and female counts per department in one row", "cancellation rate per city", "revenue per month as columns". The answer is always GROUP BY the row label plus one SUM(CASE …) per column.

In one line

SUM(CASE WHEN … THEN 1 ELSE 0 END) or COUNT(*) FILTER (WHERE …) counts some rows per group; one per column makes a pivot.