The filter
clause extends aggregate functions (sum
, avg
, count
, …) by an additional where
clause. The result of the aggregate is built from only the rows that satisfy the additional where
clause too.
Syntax
The filter
clause follows an aggregate function:
SUM(<expression>) FILTER(WHERE <condition>)
With the exception of subqueries and window functions, the <condition>
may contain any expression that is allowed in regular where
clauses.0
The filter
clause works for any aggregate function: besides the well-known functions such as sum
and count
, it also works for array_agg
and ordered set functions (e.g., listagg
).
If an aggregate function is used as a window function (over
clause), the syntactic order is: aggregate function, filter
clause, over
clause:
SUM(...) FILTER(WHERE ...) OVER (...)
However, the filter
clause is not generally allowed before over
rather, it is only allowed after an aggregate function, but not after other window functions—it is not allowed with ranking functions (rank
, dense_rank
, etc.) for example.
Use Cases
The following articles describe common use cases of filter
:
Pivot—Rows to Columns —
filter
in theselect
clauseMore to come: Subscribe to the newsletter!
Compatibility
SQL:2003 introduced the filter
clause as part of the optional feature “Advanced OLAP operations” (T612). It is barely supported today, but is easy to emulate using case
(see Conforming Alternatives).
Conforming Alternatives
Generally, the filter
clause can be implemented using a case
expression inside the aggregate function: the filter condition has to be put into the when
-clause, the value to be aggregated into the then
clause. Because aggregate functions generally skip over null
values,1 the implicit else null
clause is enough to ignore non-matching rows. The following two expressions are equivalent:
SUM(<expression>) FILTER(WHERE <condition>)
SUM(CASE WHEN <condition> THEN <expression> END)
Count(*)
needs some special treatment because “*” cannot be put into the then
clause. Instead, it is enough to use a non-null
constant value. This ensures that every matching row is counted. The implicit else null
clause maps non-matching rows to null
, which is ignored by count
too.
COUNT(*) FILTER (WHERE <condition>)
COUNT(CASE WHEN <condition> THEN 1 END)
When using a set quantifier (distinct
or all
) it must remain in the aggregate function prior the case
expression.
On my Own Behalf
I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.
Proprietary Extensions
PostgreSQL: Subqueries Allowed
The PostgreSQL database supports subqueries inside the filter
clause (e.g., via exists
).
Proprietary Alternatives
As the above described alternative with the case
expression is very widely supported I recommend using that approach rather than the proprietary alternatives offered by some products.
Countif
(Google BigQuery)
Google BigQuery offers the aggregate function countif
similar to Microsoft Excel.
COUNTIF(<condition>) [OVER(…)]
The same result can be obtained with the case
approach, which is very widely supported.
COUNT(CASE WHEN <condition> THEN 1 END) [OVER(…)]