CUBE
CUBE is a GROUP BY extension that computes aggregates for every possible combination of a set of grouping columns, including subtotals for each subset and a grand total.
Overview
CUBE(col1, col2, ..., colN) is shorthand for computing aggregates across every possible combination of the listed columns -- all 2^N grouping sets, from the fully detailed grouping down to the grand total. It's the symmetric counterpart to ROLLUP, which only computes a hierarchical subset of combinations in a fixed order.
Copy code
SELECT region, product, SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE (region, product);
This is equivalent to:
Copy code
GROUP BY GROUPING SETS (
(region, product),
(region),
(product),
()
);
producing revenue broken down by region and product together, by region alone, by product alone, and a grand total -- all four combinations, in a single query.
CUBE vs ROLLUP
ROLLUP (a, b, c)producesN + 1grouping sets, following a fixed hierarchy ((a,b,c),(a,b),(a),()).CUBE (a, b, c)produces2^Ngrouping sets -- every possible subset of the columns, including combinations that skip a middle column (like(a, c)withoutb), whichROLLUPnever produces.
Because CUBE's output grows exponentially with the number of columns, it's best used with a small number of grouping columns (2-4 is typical); a CUBE over many columns can produce an enormous result set.
Identifying which combination a row belongs to
As with ROLLUP and GROUPING SETS, use GROUPING(col) to determine whether a given row's NULL represents "this column was excluded from this particular combination" versus a genuine NULL in the source data:
Copy code
SELECT region, product, SUM(revenue) AS total_revenue,
GROUPING(region) AS region_omitted,
GROUPING(product) AS product_omitted
FROM sales
GROUP BY CUBE (region, product);
DuckDB notes
DuckDB implements CUBE per the ANSI SQL standard, as syntactic sugar over GROUPING SETS, and like ROLLUP and GROUPING SETS, it can't be combined with GROUP BY ALL.
Related terms
GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.
ROLLUP →ROLLUP is a GROUP BY extension that produces hierarchical subtotals -- aggregating at each level of a column list, from the most detailed grouping down to a grand total.
OLAP cube →An OLAP cube is a multidimensional data structure that pre-aggregates measures across combinations of dimensions, letting users slice, dice, drill down, and roll up data quickly without recomputing aggregations each time.
GROUP BY clause →GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
FAQS
2^N combinations for N columns -- every possible subset of the listed columns, including the empty set (grand total) and the fully detailed grouping.
ROLLUP only produces a fixed hierarchical sequence of N+1 groupings by dropping columns from the right; CUBE produces every possible combination of the columns, including ones ROLLUP skips, such as grouping by the first and third column while omitting the second.
Yes -- because the number of grouping sets doubles with each additional column, CUBE is typically used with only a small number of columns (roughly 2-4) to keep the result size and computation manageable.
