The FILTER Clause

## The clause and where it goes The standard's aggregate-function syntax allows an optional *filter clause* after the argument list: ```sql COUNT(*) FILTER (WHERE status = 'paid') SUM(amount) FILTER (WHERE status = 'paid') ``` The predicate inside `FILTER (WHERE …)` is an ordinary row predicate. Rows for which it is TRUE are fed into that aggregate; rows for which it is FALSE or UNKNOWN are simply not fed into it. Nothing else about the query changes. ## Two different scopes A query can filter at three different scopes, and confusing them is the whole point of this question. - **`WHERE`** filters the query's rows. It runs before grouping, so it decides which rows exist at all: every aggregate in the SELECT list sees only survivors, group membership is computed from survivors, and rows it removes cannot come back. - **`FILTER`** filters *one aggregate's input*. The rows still exist in the query, still form groups, and still feed the other aggregates. - **`HAVING`** filters whole groups after aggregation, using aggregate results. So `WHERE` is subtractive for the entire statement; `FILTER` is subtractive for exactly one measure. ## Worked example ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(*) FILTER (WHERE status = 'paid') AS orders_paid FROM orders GROUP BY customer_id; ``` A customer with five orders, two of them paid, produces `(5, 2)`. Rewrite it as ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(*) AS orders_paid FROM orders WHERE status = 'paid' GROUP BY customer_id; ``` and the same customer produces `(2, 2)` — the "total" is no longer a total. Worse, a customer with five orders and *none* paid disappears from the result entirely, because none of their rows survived `WHERE` and so no group was formed for them. With `FILTER` that customer is still returned, as `(5, 0)`. ## What FILTER buys you Before the filter clause (and still, on engines that lack it) the usual way to compute several differently-filtered measures side by side was conditional aggregation with `CASE`, or several correlated subqueries or self-joins over the same table. `FILTER` expresses the intent directly and lets one grouping pass produce many measures: ```sql SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE status = 'paid') AS paid, COUNT(*) FILTER (WHERE status = 'refunded')AS refunded, SUM(amount) FILTER (WHERE status = 'paid') AS revenue FROM orders; ``` Each aggregate carries its own condition, and the reader does not have to decode a stack of `CASE` expressions to see what is being counted. ## Where it sits in evaluation Logically: the FROM/JOIN result is filtered by `WHERE`, groups are formed by `GROUP BY`, then each aggregate consumes the rows of its group — applying its own filter predicate as it goes — and finally `HAVING`, the SELECT list and `ORDER BY` run. Two consequences follow. First, the filter predicate may reference any column of the underlying rows, including columns that are not in the `GROUP BY` list, because it is evaluated per row. Second, it may **not** contain an aggregate or a window function, which would nest an aggregate inside an aggregate. ## NULL handling in the predicate The predicate follows normal three-valued logic: only TRUE admits a row. `FILTER (WHERE bonus > 0)` silently skips rows where `bonus` is NULL, exactly as the same predicate in `WHERE` would. ## Portability The filter clause is standard SQL. PostgreSQL implements it (since 9.4) and SQLite implements it for aggregate functions (since 3.30). MySQL, SQL Server and Oracle do not accept it; on those engines you write the equivalent `CASE` form — `COUNT(CASE WHEN status = 'paid' THEN 1 END)` and `SUM(CASE WHEN status = 'paid' THEN amount END)`. Check your engine's documentation rather than assuming; support is the one thing that changes between releases. ## Typical mistakes Treating `FILTER` and `WHERE` as interchangeable is the main one; the difference only shows up when a second, differently-filtered measure or an all-non-matching group exists. The second is expecting a group to disappear when nothing in it matches the filter — it does not; `COUNT` reports 0 and `SUM`, `AVG`, `MIN`, `MAX` report NULL for that measure. The third is assuming `FILTER` is a portable spelling you can use anywhere.