Chapter 2 · 8 min

Conditional aggregation

A single grouped query can report several subsets at once by putting a CASE expression inside an aggregate. This is the standard way to turn rows into a small summary table without running one query per category.

SELECT
  customer_id,
  count(*) FILTER (WHERE status = 'paid') AS paid_orders,
  count(*) FILTER (WHERE status = 'refunded') AS refunded_orders
FROM orders
GROUP BY customer_id;

FILTER is the clearest form, and Postgres supports it directly. The portable equivalent uses a CASE that returns 1 for matching rows and NULL otherwise, because aggregates ignore nulls:

sum(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders

Do not write ELSE NULL for a count unless you mean to exclude the non-matching rows from the denominator. For a rate, the denominator should be the full group, so count every row and divide by that count.

Rates and divide-by-zero

A ratio needs a guard. NULLIF turns a zero denominator into null, and division by null yields null instead of an error:

SELECT
  channel,
  count(*) FILTER (WHERE status = 'refunded')::numeric
    / NULLIF(count(*), 0) AS refund_rate
FROM orders
GROUP BY channel;

Cast before dividing when both sides are integers; integer division truncates, so 1 / 4 is 0 rather than 0.25. The cast belongs on the numerator, not on the result.

Pivoting without a pivot function

The same pattern produces one column per category:

SELECT
  order_date,
  sum(CASE WHEN channel = 'web' THEN 1 ELSE 0 END) AS web_orders,
  sum(CASE WHEN channel = 'mobile' THEN 1 ELSE 0 END) AS mobile_orders
FROM orders
GROUP BY order_date
ORDER BY order_date;

This is a pivot written as aggregation. It is readable when the categories are few and fixed. If the categories come from data and can grow, a single row per category is usually the better shape, and the pivot belongs in the presentation layer.

The mistake to avoid

Conditional aggregation is not a substitute for WHERE when the condition applies to the whole report. Filtering the input to paid orders and then counting refunds produces a column of zeros. Decide first whether the condition selects rows or selects what is counted; the clause follows from that decision.