SQL Reference ยท Aggregate Functions
SQL FILTER clause
Reference for the SQL FILTER (WHERE ...) clause on aggregates: count or sum only some rows per group, with the CASE-based fallback for older databases.
The FILTER clause restricts an aggregate to the rows that satisfy a condition, without removing those rows from the rest of the query. It lets one SELECT compute several counts or sums over different subsets of the same group.
The standard form is aggregate(...) FILTER (WHERE condition). SQLite (3.30+) and PostgreSQL support it. Elsewhere you write the same thing with CASE, which works everywhere and is shown below.
SELECT COUNT(*) FILTER (WHERE price > 15) AS expensive FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
aggregate_function(...) FILTER (WHERE condition)
| Part | Description |
|---|---|
aggregate_function(...) |
Any aggregate: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT... In SQLite and PostgreSQL it can also be a window function call followed by OVER. |
condition |
A boolean expression evaluated per row. Rows where it is false or NULL are not fed to the aggregate. |
Returns: whatever the aggregate returns, computed over the matching rows only. If no row matches, COUNT gives 0 and the other aggregates give NULL.
Dialect differences
| Database | Support |
|---|---|
| SQLite | FILTER from version 3.30. |
| PostgreSQL | FILTER since 9.4, also with window functions. |
| MySQL | Not supported; use CASE. |
| SQL Server | Not supported; use CASE. |
| Oracle | Not supported; use CASE. |
-- MySQL, SQL Server, Oracle (and everything else) SELECT SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered FROM orders;
Usage notes
- Same result as
CASE.AVG(CASE WHEN c THEN x END)relies onCASEreturningNULLwhen nothing matches and on the aggregate skippingNULLs.FILTERstates the intent more clearly. - Careful with
COUNT(CASE ...). WriteCOUNT(CASE WHEN c THEN 1 END)with noELSE; anELSE 0would be counted, because 0 is notNULL. - Different from
WHERE.WHEREremoves rows from every aggregate in the query.FILTERaffects one aggregate only, which is what makes side-by-side comparisons possible. - Empty subsets.
SUM(...) FILTER (...)returnsNULLwhen nothing matches; wrap it in COALESCE if you need 0. - Pivoting. A list of
FILTERorCASEaggregates, one per category, is the standard way to turn rows into columns. - Readability and speed. One scan computing five conditional aggregates is usually cheaper than five separate queries.
Related
- COUNT(), SUM(), CASE, pivoting data, GROUP BY, HAVING
- Lessons: CASE, Pivot tables