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, 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:

• 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, DB2, DUCKDB, HANA, ORACLE, POSTGRES, SNOWFLAKE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA
SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY GROUPING SETS (
  (BOOK.AUTHOR_ID),
  (BOOK.LANGUAGE_ID)
)

-- ACCESS, ASE, 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.19, see #10141), or translate your own on our website

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

previous : next