FILTER over CASE in aggregate

Claim: Performance

Using SUM(CASE WHEN condition THEN value ELSE 0 END) or COUNT(CASE WHEN condition THEN 1 END) obscures intent. The FILTER (WHERE ...) clause separates filtering logic from aggregation logic, making the query easier to read.

The two forms return the same result when the CASE has no ELSE, or ELSE NULL. With ELSE 0 they differ, because every row that fails the condition becomes a 0 the aggregate reads:

SUM returns 0 where the FILTER form returns NULL, when rows are read but none match.
COUNT counts every row, because 0 is not NULL.
AVG averages the zeros in, and MIN or MAX can return 0.

FILTER is a SQL standard feature supported since PostgreSQL 9.4.

Example

-- Less readable
SELECT
  SUM(CASE WHEN status = 'active' THEN amount END) AS active_total,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders;

-- More readable, same results
SELECT
  SUM(amount) FILTER (WHERE status = 'active') AS active_total,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders;

Why it matters

The CASE version mixes two concerns — what to aggregate and which rows to include — into a single expression. The FILTER version separates them:

The aggregate function states what to compute (SUM(amount), COUNT(*))
The FILTER clause states which rows to include (WHERE status = 'active')

This separation makes complex queries with multiple conditional aggregates easier to scan.

How to fix it

Replace the CASE expression with a FILTER clause on the aggregate:

Pattern	Rewrite	Same result
`SUM(CASE WHEN cond THEN val END)`	`SUM(val) FILTER (WHERE cond)`	Yes
`COUNT(CASE WHEN cond THEN 1 END)`	`COUNT(*) FILTER (WHERE cond)`	Yes
`AVG(CASE WHEN cond THEN val ELSE NULL END)`	`AVG(val) FILTER (WHERE cond)`	Yes
`SUM(CASE WHEN cond THEN val ELSE 0 END)`	`SUM(val) FILTER (WHERE cond)`	No: `NULL` instead of 0 when no row matches
`COUNT(CASE WHEN cond THEN 1 ELSE 0 END)`	`COUNT(*) FILTER (WHERE cond)`	No: the `CASE` form counts every row

For SUM with ELSE 0, wrap the rewrite in COALESCE(..., 0) if the caller relies on getting 0. A COUNT with ELSE 0 counts every row, so if the query meant to count the matching rows, the FILTER form also fixes a wrong result.

Detection

Query Doctor reports this Finding when an aggregate function (SUM, COUNT, AVG, MIN, MAX) has a CASE expression as its argument, with a single WHEN clause and an optional ELSE 0 or ELSE NULL. It reports the ELSE 0 forms too, where the rewrite changes the result as described above. Complex CASE expressions with multiple WHEN clauses are not flagged, as they may not have a straightforward FILTER equivalent.

FILTER over CASE in aggregate

Example

Why it matters

How to fix it

Detection

References