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

GROUP BY GROUPING SETS

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In reports, it may be useful to run multiple aggregations across multiple dimensions of the data in one go. GROUPING SETS is one way to do this.

SELECT AUTHOR_ID, LANGUAGE_ID, COUNT(*)
FROM BOOK
GROUP BY GROUPING SETS ((AUTHOR_ID), (LANGUAGE_ID))
 
create.select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count())
      .from(BOOK)
      .groupBy(groupingSets(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))
      .fetch();

The above is a more concise (and possibly more performant) form of writing the following UNION ALL query:

SELECT AUTHOR_ID, NULL AS LANGUAGE_ID, COUNT(*)
FROM BOOK
GROUP BY AUTHOR_ID
UNION ALL
SELECT NULL, LANGUAGE_ID, COUNT(*)
FROM BOOK
GROUP BY LANGUAGE_ID

Note that the most common GROUPING SETS specifications have a dedicated, special syntax:

Dialect support

This example using jOOQ:

select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count()).from(BOOK).groupBy(groupingSets(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))

Translates to the following dialect specific expressions:

Aurora Postgres, ClickHouse, DB2, DuckDB, Hana, Oracle, Postgres, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica

SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY GROUPING SETS (
  (BOOK.AUTHOR_ID),
  (BOOK.LANGUAGE_ID)
)

ASE, Access, Aurora MySQL, BigQuery, CockroachDB, Derby, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, YugabyteDB

/* UNSUPPORTED */

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

Feedback

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

The jOOQ Logo