filter

Selective Aggregates


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, window functions and outer references, 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:

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).

BigQueryDb2 (LUW)MariaDBMySQLaOracle DBPostgreSQLSQL ServerSQLitefilter clauseEmulation using case
  1. The filter_plugin extension (3rd party) rewrites filter to case using regular expressions.

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.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

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.

BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLiteCountif

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.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. ISO/IEC 9075-2:2023 §10.9 SR 8.

    When emulating using case only window functions are disallowed. ISO/IEC 9075-2:2023 §10.9 SR 7a.

  2. ISO/IEC 9075-2:2023 §10.9 GR 7a, 8a, 10d. See also ISO/IEC 9075-2:2023 §4.18.4

    Exception: array_agg (ISO/IEC 9075-2:2023 §10.9 GR 12gii (NOTE 528)) — the subquery array construction can remove null values without filter clause.

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2015-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR