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

SQL CUBE Compatibility Across Databases
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	Native	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	Native	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	Native	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.

Details

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

SELECT region, product, SUM(sales) AS total_sales FROM orders GROUP BY CUBE(region, product); -- Produces: (region,product), (region), (product), and () grand total

Version Support

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

Per-Database Syntax & Notes

MySQL Alternative syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Alternative syntax

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;