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 offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.
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(…)]
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
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 all sizes.
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.