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.
Overview
GROUPING SETS extends GROUP BY to compute aggregates over several different groupings in a single query, instead of writing multiple queries and UNION ALL-ing them together. Each grouping set produces its own set of aggregated rows, and all of them are combined into one result set.
Copy code
SELECT region, product, SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
(region, product), -- revenue per region and product
(region), -- subtotal per region
() -- grand total across everything
);
Columns not included in a particular grouping set appear as NULL in the rows produced by that set -- for example, the (region) grouping set rows will have product as NULL, and the () (empty) set produces a single grand-total row with both region and product as NULL.
ROLLUP and CUBE as shorthand
ROLLUP and CUBE are syntactic sugar for common, structured GROUPING SETS patterns -- ROLLUP for hierarchical subtotals, CUBE for every possible combination. GROUPING SETS is the more general, fully explicit form when you need a specific combination of groupings that doesn't match either shorthand's pattern.
Distinguishing a NULL subtotal from a real NULL value
Since grouping sets produce NULL both for "this column wasn't part of this grouping" and for genuine NULL values in the data, SQL provides the GROUPING() function to disambiguate:
Copy code
SELECT region, product, SUM(revenue) AS total_revenue,
GROUPING(region) AS is_region_subtotal,
GROUPING(product) AS is_product_subtotal
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());
GROUPING(col) returns 1 when the row represents a subtotal that omitted col, and 0 when col was part of that grouping.
DuckDB notes
DuckDB implements GROUPING SETS, ROLLUP, and CUBE per the ANSI SQL standard. Note that this explicit grouping syntax is not compatible with DuckDB's GROUP BY ALL shorthand, which infers grouping columns automatically.
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.
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.
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.
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.
NULL →NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
FAQS
It achieves the same result in a single query and a single pass over the data, which is typically far more efficient than computing each grouping separately and combining them afterward.
It can mean either that the column wasn't part of that particular grouping (a subtotal row) or that the underlying data genuinely contains a NULL -- use the GROUPING() function to tell the two cases apart.
Use ROLLUP or CUBE when you need their specific structured pattern (hierarchical subtotals or all combinations); use GROUPING SETS directly when you need an arbitrary, specific combination of groupings that doesn't match either shorthand.
