Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Filtering
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL standard specifies an optional FILTER clause, that can be appended to all aggregate functions including aggregated window functions. This is very useful, for example, to implement "pivot" tables, such as the following:
SELECT count(*), count(*) FILTER (WHERE TITLE LIKE 'A%'), count(*) FILTER (WHERE TITLE LIKE '%A%') FROM BOOK
create.select(
count(),
count().filterWhere(BOOK.TITLE.like("A%")),
count().filterWhere(BOOK.TITLE.like("%A%")))
.from(BOOK)
Producing:
+-------+-------+-------+ | count | count | count | +-------+-------+-------+ | 4 | 1 | 2 | +-------+-------+-------+
Or, with GROUP BY:
SELECT AUTHOR_ID, count(*), count(*) FILTER (WHERE TITLE LIKE 'A%'), count(*) FILTER (WHERE TITLE LIKE '%A%') FROM BOOK GROUP BY AUTHOR_ID
create.select(
BOOK.AUTHOR_ID,
count(),
count().filterWhere(BOOK.TITLE.like("A%")),
count().filterWhere(BOOK.TITLE.like("%A%")))
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID)
Producing:
+-----------+-------+-------+-------+ | AUTHOR_ID | count | count | count | +-----------+-------+-------+-------+ | 1 | 2 | 1 | 1 | | 2 | 2 | 0 | 1 | +-----------+-------+-------+-------+
It is usually a good idea to calculate multiple aggregate functions in a single query, if this is possible, and FILTER helps here.
Only a few dialects implement native support for the FILTER clause. In all other databases, jOOQ emulates the clause using a CASE expression. Aggregate functions exclude NULL values from aggregation.
Dialect support
This example using jOOQ:
count().filterWhere(BOOK.TITLE.like("A%"))
Translates to the following dialect specific expressions:
Access
count(SWITCH(BOOK.TITLE LIKE 'A%', 1))
ASE, Aurora MySQL, DB2, Derby, Exasol, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
count(CASE WHEN BOOK.TITLE LIKE 'A%' THEN 1 END)
Aurora Postgres, CockroachDB, DuckDB, Firebird, H2, HSQLDB, Postgres, SQLite, Trino, YugabyteDB
count(*) FILTER (WHERE BOOK.TITLE LIKE 'A%')
BigQuery
countif((BOOK.TITLE LIKE 'A%'))
ClickHouse
count() FILTER (WHERE BOOK.TITLE LIKE 'A%')
Snowflake
count_if((BOOK.TITLE LIKE 'A%'))
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!