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.
Overview
ROLLUP(col1, col2, ..., colN) is shorthand for a specific, common pattern of GROUPING SETS: it produces one grouping set for the full column list, then progressively removes columns from the right, ending with the empty grouping set (a grand total). It's designed for hierarchical data -- like region -> city -> store -- where you want subtotals at every level plus an overall total.
Copy code
SELECT region, city, SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (region, city);
This is equivalent to:
Copy code
GROUP BY GROUPING SETS ((region, city), (region), ());
producing: revenue per (region, city), a subtotal per region, and a single grand-total row.
Order matters
Unlike CUBE, which is symmetric, ROLLUP's column order defines the hierarchy -- ROLLUP (region, city) assumes city rolls up into region, not the other way around. ROLLUP (city, region) would instead produce subtotals per city and treat region as the finer-grained dimension.
Identifying subtotal rows
As with any GROUPING SETS-based query, columns omitted from a particular grouping level appear as NULL. Use GROUPING(col) to distinguish a NULL that represents "this is a subtotal row" from a genuine NULL value in the underlying data:
Copy code
SELECT region, city, SUM(revenue) AS total_revenue,
GROUPING(city) AS is_region_subtotal
FROM sales
GROUP BY ROLLUP (region, city);
DuckDB notes
DuckDB implements ROLLUP per the ANSI SQL standard, as syntactic sugar over GROUPING SETS. Like GROUPING SETS and CUBE, ROLLUP cannot be combined with GROUP BY ALL.
Typical use case
Financial and sales reporting is the classic use case: a report showing revenue by day within month within quarter, with subtotal rows rolling up at each level and a grand total at the bottom -- all from a single query rather than a report-building tool stitching several separate aggregate queries together.
Related terms
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.
GROUPING SETS →GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.
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.
PARTITION BY →PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
FAQS
ROLLUP is shorthand for a specific, hierarchical pattern of grouping sets (progressively dropping columns from the right down to a grand total); GROUPING SETS lets you specify any arbitrary combination of groupings explicitly.
Yes -- ROLLUP assumes a hierarchy where each column rolls up into the ones before it, so ROLLUP (region, city) produces different subtotals than ROLLUP (city, region).
Use the GROUPING(col) function, which returns 1 for rows where col was omitted from that grouping level and 0 when col was part of the grouping.
