Conditional Aggregation

The Idea: CASE WHEN Inside an Aggregate

Conditional aggregation uses a CASE WHEN expression inside an aggregate function argument, so a single GROUP BY pass can compute several differently-filtered aggregates side by side — one column per condition — instead of running multiple GROUP BY queries (one per condition) and stitching results together.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Motivating Problem

Without conditional aggregation, getting "revenue from Widget orders" and "revenue from Gadget orders" per region as separate columns would require either two separate queries or a self-join. With CASE WHEN inside SUM, one query does it:

sql
SELECT region,
       SUM(CASE WHEN product = 'Widget' THEN amount ELSE 0 END) AS widget_revenue,
       SUM(CASE WHEN product = 'Gadget' THEN amount ELSE 0 END) AS gadget_revenue
FROM orders
GROUP BY region
ORDER BY region;

Result:

Each SUM(CASE WHEN ...) scans the same group's rows once, but only adds amount into the running total when the condition matches (and adds 0 — a neutral value for SUM — otherwise). The row-per-region granularity of a plain GROUP BY region is preserved; the pivoting into per-product columns happens inside each aggregate.

Conditional COUNT

The same pattern works for counts — omit the ELSE (or use ELSE NULL, which is the default) so COUNT only counts matching rows, since COUNT(expr) ignores NULLs:

sql
SELECT region,
       COUNT(CASE WHEN product = 'Widget' THEN 1 END) AS widget_orders,
       COUNT(CASE WHEN product = 'Gadget' THEN 1 END) AS gadget_orders
FROM orders
GROUP BY region
ORDER BY region;

Note the ELSE branch is omitted here on purpose — a CASE with no matching WHEN and no ELSE returns NULL, and COUNT(expr) never counts NULLs. If you had instead used ELSE 0, COUNT would incorrectly count every row (since 0 is not NULL), inflating the result — a common bug.

A Compact Pivot Alternative Style

FILTER (PostgreSQL-specific, standard SQL:2003) does the same thing more readably:

sql
SELECT region,
       SUM(amount) FILTER (WHERE product = 'Widget') AS widget_revenue,
       SUM(amount) FILTER (WHERE product = 'Gadget') AS gadget_revenue
FROM orders
GROUP BY region
ORDER BY region;

This gives identical results to the CASE WHEN version but reads more clearly; it is not portable to every dialect (e.g., not in standard MySQL), whereas CASE WHEN works virtually everywhere.

Edge Cases

• SUM's ELSE must be a neutral value (0), but COUNT's "ELSE" should be absent/NULL, not 0 — mixing these up is the single most common conditional-aggregation bug.
• AVG with CASE WHEN and ELSE 0 is wrong — it would count non-matching rows as zeros in the denominator, skewing the average down. Use AVG(CASE WHEN cond THEN amount END) with no ELSE (defaults to NULL, which AVG correctly ignores) instead.
• Conditional aggregation composes with HAVING: e.g. HAVING SUM(CASE WHEN product='Widget' THEN amount ELSE 0 END) > 200.

Key Takeaways / Interview Q&A

• Q: Why use CASE WHEN inside SUM instead of running one query per product?

A: It computes all conditional totals in a single pass over the data, in one row per group, instead of separate queries that each rescan the table.

• Q: What is East's widget_revenue here?

A: 160 (orders 1 and 8 combined: 100 + 60).

• Q: What's the bug in `COUNT(CASE WHEN product='Widget' THEN 1 ELSE 0 END)`?

A: It counts every row (since ELSE 0 is never NULL), not just Widget rows — omit the ELSE entirely so non-matches become NULL and get skipped by COUNT.

• Q: What's the PostgreSQL shorthand for conditional aggregation?

A: agg(expr) FILTER (WHERE condition).