Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT COUNT(*) FILTER (WHERE price > 15) AS expensive
FROM books;

Try it

SQL Several conditional counts in one pass ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL The portable CASE version gives the same answer ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Pivot-style report per group ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 on CASE returning NULL when nothing matches and on the aggregate skipping NULLs. FILTER states the intent more clearly.
  • Careful with COUNT(CASE ...). Write COUNT(CASE WHEN c THEN 1 END) with no ELSE; an ELSE 0 would be counted, because 0 is not NULL.
  • Different from WHERE. WHERE removes rows from every aggregate in the query. FILTER affects one aggregate only, which is what makes side-by-side comparisons possible.
  • Empty subsets. SUM(...) FILTER (...) returns NULL when nothing matches; wrap it in COALESCE if you need 0.
  • Pivoting. A list of FILTER or CASE aggregates, 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.