Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
GROUP BY CUBE
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. CUBE is one way to do this.
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*) FROM BOOK GROUP BY CUBE (AUTHOR_ID, PUBLISHED_IN)
create.select(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN, count())
.from(BOOK)
.groupBy(cube(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, PUBLISHED_IN, COUNT(*) FROM BOOK GROUP BY AUTHOR_ID, PUBLISHED_IN UNION ALL SELECT AUTHOR_ID, NULL, COUNT(*) FROM BOOK GROUP BY AUTHOR_ID SELECT NULL, PUBLISHED_IN, COUNT(*) FROM BOOK GROUP BY LANGUAGE_ID UNION ALL SELECT NULL, NULL, COUNT(*) FROM BOOK GROUP BY ()
The CUBE function is just syntax sugar for a more complex GROUPING SETS specification. In general:
-- This CUBE (A, B, C) -- Is just short for this GROUPING SETS ((A, B, C), (A, B), (A, C), (B, C), (A), (B), (C), ())
An example result set might look like this:
+-----------+--------------+----------+ | AUTHOR_ID | PUBLISHED_IN | COUNT(*) | +-----------+--------------+----------+ | NULL | NULL | 4 | <- GROUP BY () | 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) | 1 | 1945 | 1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN) | 1 | 1948 | 1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN) | 2 | NULL | 2 | <- GROUP BY (AUTHOR_ID) | 2 | 1988 | 1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN) | 2 | 1990 | 1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN) +-----------+--------------+----------+
Dialect support
This example using jOOQ:
select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count()).from(BOOK).groupBy(cube(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))
Translates to the following dialect specific expressions:
Aurora Postgres, ClickHouse, DB2, Databricks, DuckDB, Hana, Oracle, Postgres, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica
SELECT BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count(*) FROM BOOK GROUP BY CUBE (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 */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!