GROUPING SETSQuery Techniques
Buy Me A Coffee

An explicit list of grouping combinations to compute in a single pass, giving precise control over which subtotals are produced. More flexible than ROLLUP or CUBE.

An explicit GROUP BY variant that specifies exactly which grouping combinations to compute. More flexible than ROLLUP or CUBE, and clearer in intent. SELECT region, city, SUM(sales) GROUP BY GROUPING SETS((region), (region, city), ()) computes totals at multiple levels.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — GROUPING SETS is not supported. MySQL only supports ROLLUP via GROUP BY ... WITH ROLLUP or GROUP BY ROLLUP(...). For specific grouping combinations, use UNION ALL.
PostgreSQL ✓ Supported 9.5 Standard GROUPING SETS syntax. Can be combined with ROLLUP and CUBE in the same GROUP BY clause. GROUPING(col) identifies which rows are grouping-generated. An empty set () produces the grand total. Equivalent to the corresponding UNION ALL but computed in a single scan.
SQL Server ✓ Supported 2008 Standard GROUPING SETS syntax since SQL Server 2008. GROUPING() and GROUPING_ID() supported. Useful for reporting queries that need specific subtotal combinations without the full output of CUBE.
Oracle ✓ Supported 8i Standard GROUPING SETS syntax since Oracle 8i. Can be combined with ROLLUP and CUBE. GROUPING() and GROUPING_ID() supported.
SQLite ✗ Not Supported — GROUPING SETS is not supported. Use UNION ALL to combine individual GROUP BY queries for each desired grouping.

Details

GROUPING SETS is the most explicit of the three grouping extensions (vs ROLLUP and CUBE): you list exactly the combinations you want, nothing more. ROLLUP(a, b) is syntactic sugar for GROUPING SETS((a,b),(a),()), and CUBE(a,b) is syntactic sugar for GROUPING SETS((a,b),(a),(b),()). MySQL is the only major RDBMS with no support for GROUPING SETS; SQLite supports none of the three. When genuinely NULL data values exist in grouping columns, always use GROUPING() to distinguish subtotal NULLs from real NULLs — COALESCE alone is unreliable in that case.

Standard Syntax

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY GROUPING SETS ( (region, product), (region), () );

Version Support

MySQL: Not supported PostgreSQL: Since 9.5 SQL Server: Since 2008 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

GROUPING SETS is not supported. MySQL only supports ROLLUP via GROUP BY ... WITH ROLLUP or GROUP BY ROLLUP(...). For specific grouping combinations, use UNION ALL.

-- UNION ALL workaround: SELECT region, product, SUM(sales) AS total FROM orders GROUP BY region, product UNION ALL SELECT region, NULL, SUM(sales) FROM orders GROUP BY region UNION ALL SELECT NULL, NULL, SUM(sales) FROM orders;

PostgreSQL

Standard GROUPING SETS syntax. Can be combined with ROLLUP and CUBE in the same GROUP BY clause. GROUPING(col) identifies which rows are grouping-generated. An empty set () produces the grand total. Equivalent to the corresponding UNION ALL but computed in a single scan.

-- Explicit sets: SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY GROUPING SETS ( (region, product), (region), () ); -- Combining with ROLLUP: SELECT region, product, channel, SUM(sales) FROM orders GROUP BY GROUPING SETS ( ROLLUP(region, product), (channel) ); -- Identify grand total row: SELECT CASE GROUPING(region) WHEN 1 THEN 'TOTAL' ELSE region END AS region, CASE GROUPING(product) WHEN 1 THEN 'TOTAL' ELSE product END AS product, SUM(sales) AS total_sales FROM orders GROUP BY GROUPING SETS ((region, product), (region), ());

SQL Server

Standard GROUPING SETS syntax since SQL Server 2008. GROUPING() and GROUPING_ID() supported. Useful for reporting queries that need specific subtotal combinations without the full output of CUBE.

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY GROUPING SETS ( (region, product), (region), () ); -- GROUPING_ID returns integer bitmask: SELECT region, product, SUM(sales) AS total_sales, GROUPING_ID(region, product) AS level FROM orders GROUP BY GROUPING SETS ((region, product), (region), ());

Oracle

Standard GROUPING SETS syntax since Oracle 8i. Can be combined with ROLLUP and CUBE. GROUPING() and GROUPING_ID() supported.

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY GROUPING SETS ( (region, product), (region), () ); -- Combining ROLLUP and GROUPING SETS: SELECT region, product, channel, SUM(sales) FROM orders GROUP BY GROUPING SETS ( ROLLUP(region, product), (channel) );

SQLite

GROUPING SETS is not supported. Use UNION ALL to combine individual GROUP BY queries for each desired grouping.

-- UNION ALL workaround: SELECT region, product, SUM(sales) AS total FROM orders GROUP BY region, product UNION ALL SELECT region, NULL, SUM(sales) FROM orders GROUP BY region UNION ALL SELECT NULL, NULL, SUM(sales) FROM orders;