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 and window functions, the <condition> may contain any expression that is allowed in regular where clauses0.

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 sorted set functions (e.g., percentile_cont).

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

Conforming Alternatives

Generally, the filter clause can be implemented as 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 values1, 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.

Proprietary Extensions

PostgreSQL: Subqueries Allowed

The PostgreSQL database supports subqueries inside the filter clause (e.g., via exists).

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Footnotes

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

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

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license