Conditional aggregation: pivot long rows into wide columns

Long versus wide

A normal GROUP BY status produces long output: one row per category, value stacked beneath value. A report usually wants wide output: one row per entity (a day, a user), with one column per category. Pivoting is the transform between them, and the trick is to make each output column its own conditional aggregate.

Two ways to write a pivot column

When you see a report query with three separate SELECT … WHERE status='…' blocks, you’re looking at the anti-pattern this section replaces. There are two idioms for writing each pivot column inline, and they’re equivalent for counts and sums:

-- FILTER form (modern, standard, recommended):
SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'paid')    AS paid_revenue
FROM orders
GROUP BY created_at::date
ORDER BY day;

-- CASE form (portable, older, works everywhere):
SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,        -- or:
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 'paid' THEN total END)    AS paid_revenue
FROM orders
GROUP BY created_at::date;

The mechanism is the same in both: each column is an aggregate that, per row, either includes the value or contributes nothing. With FILTER, the aggregate simply skips non-matching rows. With CASE, the non-matching branch returns 0 (for a SUM-count) or NULL (which aggregates ignore). FILTER is cleaner and is the senior default; CASE is the portable fallback for engines without FILTER.

The win that matters at scale is the scan count. Both forms compute all the pivot columns in one pass — the engine evaluates every column’s condition against each row as it streams by. The anti-pattern is one query per category: three separate SELECT count(*) FROM orders WHERE status='paid' (then 'pending', then 'cancelled') is three Seq Scans of the table. On a 12M-row orders table that’s ~3 × 800ms ≈ 2.4s versus ~850ms for the single grouped query with three FILTER columns — and the gap widens linearly with the number of pivot columns: a 7-status pivot is one scan instead of seven. Conditional aggregation turns an N-scan report into a 1-scan report, which is exactly why you pivot at the source rather than firing one query per column from the app.

The CASE counting trap

The single most common bug here is counting with the wrong fallback:

-- WRONG: COUNT counts non-NULL, and 0 is non-NULL → counts EVERY row
COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)   -- always = total rows

-- RIGHT options:
COUNT(CASE WHEN status = 'paid' THEN 1 END)          -- ELSE is NULL → COUNT skips it
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     -- SUM adds 1s, 0s contribute nothing
COUNT(*) FILTER (WHERE status = 'paid')              -- cleanest

COUNT counts non-NULL values; 0 is non-NULL, so COUNT(CASE … ELSE 0 END) counts all rows — a silent off-by-everything error. Either drop the ELSE (so non-matches are NULL and COUNT skips them), use SUM of 1/0, or use FILTER. This is exactly the pitfall FILTER was added to eliminate.

Combining with GROUP BY and totals

Conditional aggregation composes with everything you’ve learned. Add a GROUP BY entity to get one wide row per entity; layer ROLLUP from the previous lesson to add a totals row across all days:

SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY ROLLUP (created_at::date)
ORDER BY day NULLS LAST;

This is also the manual cousin of the crosstab function in the tablefunc extension and of window functions: window functions (next unit, 04-window-functions) keep one row per input row while adding aggregate columns, whereas conditional aggregation collapses rows and spreads categories across columns. Pick conditional aggregation when you want a compact pivot table; reach for windows when you need to keep every detail row alongside its group’s aggregate.