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 empty grouping set
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A special kind of GROUPING SET is the empty grouping set, which can be achieved in standard SQL and many SQL dialects using GROUP BY ()
. It is implicit, whenever an aggregate function is present in a query, but not an explicit GROUP BY clause.
SELECT COUNT(*) FROM BOOK GROUP BY ()
create.selectCount() .from(BOOK) .groupBy() .fetch();
Dialect support
This example using jOOQ:
selectCount().from(BOOK).groupBy()
Translates to the following dialect specific expressions:
Access
SELECT count(*) FROM BOOK, (select count(*) dual from MSysResources) as empty_grouping_dummy_table GROUP BY empty_grouping_dummy_table.dual
ASE, SQLDataWarehouse
SELECT count(*) FROM BOOK, (select 1 as dual) as empty_grouping_dummy_table GROUP BY empty_grouping_dummy_table.dual
Aurora MySQL, MemSQL
SELECT count(*) FROM BOOK GROUP BY (SELECT 1 FROM DUAL)
Aurora Postgres, BigQuery, DB2, DuckDB, Exasol, H2, Oracle, Postgres, SQLServer, Sybase, Teradata, Trino
SELECT count(*) FROM BOOK GROUP BY ()
ClickHouse, CockroachDB, MariaDB, MySQL, Redshift, SQLite, Vertica, YugabyteDB
SELECT count(*) FROM BOOK GROUP BY (SELECT 1)
Derby, HSQLDB
SELECT count(*) FROM BOOK GROUP BY 0
Firebird
SELECT count(*) FROM BOOK GROUP BY (SELECT 1 FROM RDB$DATABASE)
Hana, Snowflake
SELECT count(*) FROM BOOK GROUP BY GROUPING SETS (())
Informix
SELECT count(*) FROM BOOK, (select 1 as dual from systables where tabid = 1) as empty_grouping_dummy_table GROUP BY empty_grouping_dummy_table.dual
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!