CUBE SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

An extension of GROUP BY ROLLUP that generates subtotals for every possible combination of the specified columns, not just hierarchical rollups. Produces 2^n grouping combinations for n columns.

Generates all possible combinations of the specified columns groupings, the full factorial of GROUP BY combinations. GROUP BY CUBE(a, b) produces (a, b), (a), (b), and (). Can produce a very large number of rows -- use sparingly.

Compatibility

Details

Database System	Support Status	Since Version	Notes
MySQL	✗ Not Supported	—	CUBE is not supported. MySQL only supports ROLLUP via GROUP BY ... WITH ROLLUP or GROUP BY ROLLUP(...) syntax. Workaround: use UNION ALL to manually union each combination, or use GROUPING SETS-style UNION ALL queries.
PostgreSQL	✓ Supported	9.5	Standard GROUP BY CUBE(...) syntax. GROUPING(col) available to identify which rollup-generated NULL rows represent which grouping level. For 3 columns, CUBE generates 8 grouping combinations (2^3). Can be mixed with ROLLUP and GROUPING SETS.
SQL Server	✓ Supported	2008	Standard GROUP BY CUBE(...) syntax. GROUPING(col) and GROUPING_ID() available. For very wide CUBE operations, consider breaking into GROUPING SETS for more control over which combinations are generated.
Oracle	✓ Supported	8i	Standard GROUP BY CUBE(...) syntax since Oracle 8i. GROUPING() and GROUPING_ID() supported. Can be combined with ROLLUP and GROUPING SETS in a single GROUP BY clause.
SQLite	✗ Not Supported	—	CUBE is not supported. Workaround: enumerate all 2^n combinations manually using UNION ALL.

CUBE(a, b) generates 4 groupings: (a,b), (a), (b), and (). CUBE(a, b, c) generates 8. The result set grows exponentially with column count — be cautious with high-cardinality columns. GROUPING SETS is a more surgical alternative when you only need specific combinations rather than all of them. MySQL has no CUBE support whatsoever; SQLite has neither CUBE nor ROLLUP. The COALESCE-on-NULL pattern for labeling totals is portable but breaks if the column can contain genuine NULLs — use GROUPING() for correctness.

Standard Syntax

Version Support

Per-Database Syntax & Notes

MySQL

CUBE is not supported. MySQL only supports ROLLUP via GROUP BY ... WITH ROLLUP or GROUP BY ROLLUP(...) syntax. Workaround: use UNION ALL to manually union each combination, or use GROUPING SETS-style UNION ALL queries.

-- Workaround with UNION ALL for 2 dimensions: 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, product, SUM(sales) FROM orders GROUP BY product UNION ALL SELECT NULL, NULL, SUM(sales) FROM orders;

PostgreSQL

Standard GROUP BY CUBE(...) syntax. GROUPING(col) available to identify which rollup-generated NULL rows represent which grouping level. For 3 columns, CUBE generates 8 grouping combinations (2^3). Can be mixed with ROLLUP and GROUPING SETS.

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY CUBE(region, product); -- Label the grouping rows: SELECT COALESCE(region, 'All Regions') AS region, COALESCE(product, 'All Products') AS product, SUM(sales) AS total_sales, GROUPING(region) AS is_region_total, GROUPING(product) AS is_product_total FROM orders GROUP BY CUBE(region, product);

SQL Server

Standard GROUP BY CUBE(...) syntax. GROUPING(col) and GROUPING_ID() available. For very wide CUBE operations, consider breaking into GROUPING SETS for more control over which combinations are generated.

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY CUBE(region, product); -- GROUPING_ID returns a bitmap of which cols are aggregated: SELECT region, product, SUM(sales) AS total_sales, GROUPING_ID(region, product) AS grouping_level FROM orders GROUP BY CUBE(region, product);

Oracle

Standard GROUP BY CUBE(...) syntax since Oracle 8i. GROUPING() and GROUPING_ID() supported. Can be combined with ROLLUP and GROUPING SETS in a single GROUP BY clause.

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY CUBE(region, product); -- With GROUPING: SELECT DECODE(GROUPING(region), 1, 'All', region) AS region, DECODE(GROUPING(product), 1, 'All', product) AS product, SUM(sales) AS total_sales FROM orders GROUP BY CUBE(region, product);

SQLite

CUBE is not supported. Workaround: enumerate all 2^n combinations manually using UNION ALL.

-- Manual CUBE workaround (2 dimensions = 4 queries): 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, product, SUM(sales) FROM orders GROUP BY product UNION ALL SELECT NULL, NULL, SUM(sales) FROM orders;