Conditional aggregation

The idea

A filter throws rows away. A CASE inside an aggregate does something gentler: it lets every row through, but turns it into a 1 or a 0 for each measure you care about.

So one column becomes “how many shipped”, another “how many pending”, another “how many cancelled”: all from the same sweep of the same rows. That is a pivot, without any pivot feature.

Rates fall out of the same trick: count the matches on top, count everything underneath. The alternative is running one filtered query per measure, which reads the whole table again each time for no new information.

One sweep, three counters

In single pass every row is evaluated by all three gates at once. In three filtered scans only one gate is live per scan, so the same table is dragged past three times, and the rate’s denominator still needs a fourth.

How it works

Inside an aggregate, CASE maps each row to a number: 1 when the condition holds, 0 when it does not. SUM then adds those numbers up, which is exactly a conditional count. Because all the accumulators live in the same aggregation, the engine fills them during one scan, and a GROUP BY turns the result into a pivot table: one row per group, one column per condition.

-- one sweep, five measures, one table read
SELECT
  region,
  COUNT(*)                                                    AS orders,
  SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END)       AS shipped,
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END)       AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)       AS cancelled,
  ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
              / COUNT(*), 1)                                  AS cancel_pct
FROM orders
WHERE ordered_at >= DATE '2024-01-01'   -- rows no measure wants
GROUP BY region;

-- money works the same way; use no ELSE when averaging,
-- so non-matching rows drop out instead of averaging in as zero
SELECT
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped_revenue,
  AVG(CASE WHEN status = 'shipped' THEN amount END)        AS avg_shipped_order
FROM orders;

-- postgres/sqlite shorthand for the counting case
SELECT COUNT(*) FILTER (WHERE status = 'shipped') AS shipped FROM orders;

Cost

For n rows and k measures, with g groups:

The CPU work is the same either way; what you save is I/O and round trips. The trade-off: conditional aggregation always pays for a full pass, so if one measure is very selective and indexed (status = 'cancelled' on 0.1% of rows), a narrow index scan can beat it. The sweet spot is several measures over the same broad set of rows.

Watch out for

• Dropping ELSE 0 when you take an average. SUM(CASE WHEN c THEN 1 END) still counts correctly, because SUM skips nulls. But AVG(CASE WHEN c THEN 1 END) skips them too: the non-matching rows leave the denominator and the “rate” is always 1.0. For rates, keep the ELSE 0.
• COUNT(CASE WHEN c THEN 1 ELSE 0 END) counts everything. COUNT counts non-null values, and 0 is not null, so this returns COUNT(*). Use SUM(CASE … ELSE 0 END), or COUNT(CASE WHEN c THEN 1 END) with no ELSE.
• Integer division. In Postgres, SUM(...)/COUNT(*) is bigint ÷ bigint and silently truncates to 0. Multiply by 1.0 or cast to numeric first.
• Moving the condition into WHERE. WHERE runs before aggregation, so adding WHERE status = 'shipped' quietly zeroes every other CASE column. WHERE is only for rows that no measure wants.
• Nulls falling between buckets. If status can be null, it matches none of your conditions and disappears from all three counts while still sitting in COUNT(*). Add an explicit WHEN status IS NULL bucket, or assert that the buckets sum to the total.

Worked example

Take the nine orders in the widget above: five shipped, two pending, two cancelled. A single scan carries every row past all three CASE gates, so after nine reads the accumulators hold 5, 2 and 2, and COUNT(*) holds 9: the cancel rate is 2 / 9 = 22.2%, assembled from the same pass, not a second query.

Now scale it: 1.2 million orders, and the dashboard wants those three counts plus the cancel rate per region. Written as four filtered queries it is four full scans and four round trips. Written as one SELECT … GROUP BY region with four conditional aggregates, the planner reads the table once, keeps four small counters per region in the hash aggregate, and hands back a finished pivot (region, shipped, pending, cancelled, cancel_pct) ready to render.

Check yourself