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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

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, ClickHouse, CockroachDB, DuckDB, Firebird, H2, HSQLDB, Postgres, SQLite, Trino, YugabyteDB

count(*) FILTER (WHERE BOOK.TITLE LIKE 'A%')

BigQuery

countif((BOOK.TITLE LIKE 'A%'))

Snowflake

count_if((BOOK.TITLE LIKE 'A%'))

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo