skip to content

Your engine rejects FILTER (WHERE …) on an aggregate — how do you rewrite it portably?

level: middleimportance: should knowfreq 50%

answer

  1. Not every engine implements the clause
  2. Push the condition into the aggregate's argument
  3. Aggregates ignore NULL inputs
  4. A CASE with no ELSE yields NULL
  5. ELSE 0 breaks a conditional COUNT

basics

~20 s

Move the predicate inside the aggregate as a CASE expression with no ELSE: COUNT(*) FILTER (WHERE c) becomes COUNT(CASE WHEN c THEN 1 END), and SUM(x) FILTER (WHERE c) becomes SUM(CASE WHEN c THEN x END). Non-matching rows become NULL and aggregates skip NULLs.

solid answer

~50 s

The rewrite relies on aggregates ignoring NULL inputs. Replace the argument with a `CASE` that yields a value only when the predicate holds and **no ELSE branch**, so non-matching rows contribute NULL and are skipped: - `COUNT(*) FILTER (WHERE c)` → `COUNT(CASE WHEN c THEN 1 END)` - `SUM(x) FILTER (WHERE c)` → `SUM(CASE WHEN c THEN x END)` - `AVG(x) FILTER (WHERE c)` → `AVG(CASE WHEN c THEN x END)` - `COUNT(DISTINCT x) FILTER (WHERE c)` → `COUNT(DISTINCT CASE WHEN c THEN x END)` The classic error is adding `ELSE 0` to a `COUNT`: `COUNT(CASE WHEN c THEN 1 ELSE 0 END)` counts every row, because 0 is not NULL. You need the rewrite because `FILTER` is standard SQL but not universally implemented — PostgreSQL and SQLite accept it, while MySQL, SQL Server and Oracle do not.

code

sql · 4 lines
sql
-- Not portable: rejected by MySQL, SQL Server and Oracle
SELECT COUNT(*)    FILTER (WHERE status = 'paid')  AS paid_orders,
       SUM(amount) FILTER (WHERE status = 'paid')  AS paid_revenue
FROM orders;

go deeper

for a junior

Memorise the two core translations — COUNT(CASE WHEN c THEN 1 END) and SUM(CASE WHEN c THEN x END) — and remember that adding ELSE 0 to the COUNT form breaks it.

for a middle

Explain the mechanism, not the pattern: aggregates skip NULL inputs and a CASE without ELSE yields NULL, which is why the rewrite is exact. Be able to translate AVG and COUNT(DISTINCT) too.

for a senior

Say where the two forms diverge — the empty-group 0-versus-NULL question — and how you keep a multi-engine reporting query honest, for instance by generating both spellings from one measure definition.

for a principal

Own the portability policy: whether the codebase targets one engine and may use the clearer standard clause, or must stay engine-neutral and pays for it in readability. Decide once and enforce it in review.

## Why a rewrite is needed at all The filter clause is standard SQL, but standard and implemented are different things. PostgreSQL has supported it since 9.4 and SQLite supports it for aggregate functions since 3.30. MySQL, SQL Server and Oracle reject the syntax. If your query must run on more than one engine — or on the one that does not have it — you write the older, universally accepted form: conditional aggregation with `CASE`. ## The mechanism the rewrite depends on Every standard aggregate except `COUNT(*)` ignores NULL inputs. `SUM`, `AVG`, `MIN`, `MAX`, `COUNT(expr)` and `COUNT(DISTINCT expr)` all skip rows whose argument is NULL. A searched `CASE` with no `ELSE` branch yields NULL when no `WHEN` matches. Put those two facts together and a `CASE` argument becomes a per-aggregate filter: matching rows supply a value, non-matching rows supply NULL, and the aggregate ignores them. ## The translation table ```sql -- filter clause -- portable equivalent COUNT(*) FILTER (WHERE c) COUNT(CASE WHEN c THEN 1 END) COUNT(x) FILTER (WHERE c) COUNT(CASE WHEN c THEN x END) SUM(x) FILTER (WHERE c) SUM(CASE WHEN c THEN x END) AVG(x) FILTER (WHERE c) AVG(CASE WHEN c THEN x END) MIN(x) FILTER (WHERE c) MIN(CASE WHEN c THEN x END) COUNT(DISTINCT x) FILTER (WHERE c) COUNT(DISTINCT CASE WHEN c THEN x END) ``` Note `COUNT(*)` has no argument to condition, so the rewrite substitutes any non-null constant — `1` is conventional. `COUNT(CASE WHEN c THEN 1 END)` counts the rows where `c` is TRUE, which is exactly what `COUNT(*) FILTER (WHERE c)` does. One caveat on `COUNT(x) FILTER (WHERE c)`: both forms also skip rows where `x` itself is NULL, so they agree — but neither equals `COUNT(*) FILTER (WHERE c)` when `x` is nullable. ## The ELSE trap The single most common bug in the rewrite is an `ELSE 0` on a `COUNT`: ```sql COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) -- counts EVERY row ``` `0` is a perfectly good non-null value, so `COUNT` counts it. The result equals `COUNT(*)` regardless of the condition, and because it is a plausible-looking number nobody notices until the report is wrong. With `SUM` the same `ELSE 0` is harmless for a non-empty group — adding zeros changes nothing — but it does change the empty case from NULL to 0, so `SUM(CASE WHEN c THEN x ELSE 0 END)` is not an exact reproduction of `SUM(x) FILTER (WHERE c)`. ## Empty-input differences to keep in mind For a group in which no row matches: - `COUNT(…)` returns `0` in both the filter and the `CASE` form. - `SUM`, `AVG`, `MIN`, `MAX` return NULL in both forms. So the rewrite is faithful as long as you leave the `ELSE` off. If the consumer needs zeros, wrap the whole aggregate: `COALESCE(SUM(CASE WHEN c THEN x END), 0)` — and use the same `COALESCE` on the filter version, so the two spellings stay interchangeable. ## Predicates with NULLs The `CASE` form inherits the filter form's three-valued logic: a `WHEN` whose condition is UNKNOWN does not match, exactly as an UNKNOWN filter predicate does not admit a row. So `FILTER (WHERE bonus > 0)` and `CASE WHEN bonus > 0 THEN …` agree on NULL `bonus`. That symmetry is what makes the rewrite mechanical rather than a judgment call. ## Readability cost, and how to manage it A report with ten measures becomes ten nested `CASE` expressions, which is genuinely harder to scan than ten filter clauses. Two mitigations help: line up the `CASE` expressions in a column so the conditions read down the page, and alias every measure with a name that states the condition (`orders_paid`, `revenue_refunded`). If the query is generated, generate the `FILTER` form for engines that accept it and the `CASE` form otherwise from the same measure definitions, rather than hand-maintaining two queries. ## Which direction interviewers ask Both. "Rewrite this without FILTER" tests whether you know why NULL-skipping makes the `CASE` form work. "This query uses SUM(CASE WHEN … THEN 1 ELSE 0 END) — simplify it" tests whether you can see through the idiom to the conditional count underneath. Answer either by naming the mechanism (aggregates skip NULLs) rather than reciting the pattern.

  • Why does leaving out the ELSE branch matter so much for COUNT?
    COUNT(expr) counts rows whose argument is not NULL. With no ELSE, a non-matching CASE yields NULL and is skipped; with ELSE 0 it yields zero, which is non-null, so COUNT counts every row in the group and silently equals COUNT(*).
  • Is SUM(CASE WHEN c THEN x ELSE 0 END) an exact replacement for SUM(x) FILTER (WHERE c)?
    Almost. For a non-empty group the added zeros do not change the sum, but when no row matches the ELSE form returns 0 while the filter form returns NULL. If the difference matters to the consumer, pick one and apply COALESCE explicitly rather than relying on the ELSE.

saying these in an interview costs you the question

  • Writes COUNT(CASE WHEN c THEN 1 ELSE 0 END) and expects a conditional count
  • Thinks FILTER is a PostgreSQL extension rather than standard SQL
  • Claims every modern engine supports FILTER
  • Moves the predicate into WHERE and breaks the other measures
  • Says the CASE form scans the table an extra time

context

resolving links…