filter_plugin
extension (3rd party) rewrites filter
to case
using regular expressionsGenerally, 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.
I make my living from SQL training, SQL tuning and consulting and my book “SQL Performance Explained”. Learn more at https://winand.at/.
The PostgreSQL database supports subqueries inside the filter
clause (e.g., via exists
).
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(…)]
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »
SQL:2016-2: §10.9, Syntax Rule 8.
When emulating using case
only window functions are disallowed. SQL:2016-2: §10.9, Syntax Rule 7a.
SQL:2016-2: §10.9, General Rule 7a (for regular aggregates), 8a (for binary set operations), 10d (for inverse set operations). See also SQL:2016-2: §4.16.4.
Exception: array_agg
(SQL:2016-2: §10.9, General Rule 12gii, explicitly in note 510) — the subquery array construction can remove null
values without filter
clause.