The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement GROUP BY clause GROUP BY GROUPING SETS

previous : next

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, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY GROUPING SETS ((AUTHOR_ID), (PUBLISHED_IN))
 

create.select(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN, count())
      .from(BOOK)
      .groupBy(groupingSets(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN))
      .fetch();

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

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

An example result set might look like this:

+-----------+--------------+----------+
| AUTHOR_ID | PUBLISHED_IN | COUNT(*) |
+-----------+--------------+----------+
|      NULL |         1945 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1948 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1988 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1990 |        1 | <- GROUP BY (PUBLISHED_IN)
|         1 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|         2 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
+-----------+--------------+----------+

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

• ROLLUP
• CUBE

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 */

The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement GROUP BY clause GROUP BY GROUPING SETS

previous : next