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.