CUBE
SQL · Grouping Rows
Theory
CUBE produces subtotals for every possible combination of the listed columns. GROUP BY CUBE (dept, role) creates the groupings (dept, role), (dept), (role) and (): details, a subtotal per department, a subtotal per role across all departments and a grand total. With n columns it creates 2 to the power n groupings.
Compared with ROLLUP, CUBE does not assume a hierarchy, so it also gives cross-cutting totals like "all Leads in every department". That makes it useful for multi-dimensional analysis and pivot-style reports, but the result grows fast: three columns already give eight grouping levels. As with ROLLUP, subtotal rows show NULL, and GROUPING(col) tells them apart from real NULLs. PostgreSQL, SQL Server, Oracle and Snowflake support CUBE; MySQL and SQLite do not.
Why we use it
Analysts often want to slice totals by each dimension on its own and in combination. CUBE delivers every slice in one query, ready for a pivot table or dashboard.
When to use it
Use CUBE when you need totals for all combinations of a few dimensions. Use ROLLUP for a single hierarchy, or GROUPING SETS when you only need a few specific levels, to keep the result small.
How it works
- GROUP BY CUBE (dept, role)
- List all combinations: (dept, role), (dept), (role), ()
- Aggregate rows for each combination
- Fill columns not in the combination with NULL
- More than 3 columns? Yes: Very large result: consider GROUPING SETS. No: Size stays manageable.
- Return all subtotal levels together
Syntax
SELECT c1, c2, AGG(c3)
FROM table
GROUP BY CUBE (c1, c2);Example
SELECT department, role, COUNT(*)
FROM employees
GROUP BY CUBE (department, role);Key points
- Superset of ROLLUP: all combinations, not just hierarchical ones
- Can produce large result sets with many columns
- CUBE of n columns gives 2^n grouping levels
- Subtotal rows show NULL; use GROUPING(col) to label them
- MySQL and SQLite lack CUBE: emulate with UNION ALL