FILTER over CASE in aggregate

Severity: Info

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.

Both approaches produce equivalent results. FILTER is a SQL standard feature supported since PostgreSQL 9.4.

Example

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

-- More readable, equivalent
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 significantly easier to scan.

How to fix it

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

Pattern	Rewrite
SUM(CASE WHEN cond THEN val ELSE 0 END)	SUM(val) FILTER (WHERE cond)
COUNT(CASE WHEN cond THEN 1 END)	COUNT(*) FILTER (WHERE cond)
AVG(CASE WHEN cond THEN val ELSE NULL END)	AVG(val) FILTER (WHERE cond)

Detection

Query Doctor flags this nudge 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. Complex CASE expressions with multiple WHEN clauses are not flagged, as they may not have a straightforward FILTER equivalent.

References

• CrunchyData: Using Postgres FILTER
• CrunchyData: Fast, Flexible Summaries with Aggregate Filters