GROUPING SETS for Flexible Report Combinations

1. What GROUPING SETS solve and what they stand for

GROUPING SETS solve a problem that frequently arises with individually tailored reports: a report should not contain every conceivable combination of grouping columns, only a deliberately selected subset of them. A sales report might, for example, need subtotals by region, by product category, and an overall grand total, but explicitly not the combination of region and product category at once, because that level of detail is not relevant to the report's audience.

Without GROUPING SETS, such a report would either have to be assembled from several separate queries combined with UNION ALL afterward, or via CUBE, which automatically generates every possible combination and then requires filtering out the unneeded rows afterward. Both alternatives are either cumbersome or waste computation on combinations nobody wants to see. GROUPING SETS solve this by explicitly listing the desired combinations, and the database computes exactly these combinations in a single table scan, no more and no less.

This article explains the base syntax of GROUPING SETS, clearly distinguishes them from ROLLUP and CUBE, shows the GROUPING function for telling apart real NULL and subtotal rows, and works through common use cases for individually tailored crosstab reports.

2. Base syntax: listing combinations explicitly

The base syntax of GROUPING SETS explicitly lists every desired combination of grouping columns in parentheses, separated by commas, within a shared GROUP BY GROUPING SETS clause. Each individual parenthesized group corresponds to its own grouping level, which appears as its own rows in the result set, exactly as if you had written a separate GROUP BY query for each combination and merged the results with UNION ALL.

An empty parenthesized group, written as (), stands for grouping over the entire table without any breakdown, that is, the classic grand total. GROUPING SETS lets you combine this grand total with any other subtotal level in the same query, without needing an additional separate query for the overall value.

-- GROUPING SETS: exactly the subtotal combinations we need, nothing more
SELECT
    region,
    product_category,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY GROUPING SETS (
    (region),              -- subtotal per region
    (product_category),    -- subtotal per category
    ()                     -- grand total across everything
)
ORDER BY region, product_category;

3. GROUPING SETS vs. multiple queries with UNION ALL

Before GROUPING SETS were introduced into the SQL standard, exactly this pattern was implemented via several separate GROUP BY queries subsequently merged with UNION ALL into a shared result. Functionally, this approach delivers the same result as GROUPING SETS, but it has a decisive drawback: every separate query scans the underlying table independently, so with three desired combinations, the same data is read three times instead of just once.

GROUPING SETS solve this problem because modern database query planners read the table only once and derive the various grouping levels from that single data pass, similar to what already happens with ROLLUP and CUBE. The performance difference between UNION ALL over separate queries and GROUPING SETS grows proportionally with the number of desired combinations and the size of the underlying table, which makes GROUPING SETS the clearly superior choice for reports with many subtotal levels.

-- The old way: three table scans combined with UNION ALL
SELECT region, NULL AS product_category, SUM(total_amount) AS revenue
FROM orders GROUP BY region
UNION ALL
SELECT NULL, product_category, SUM(total_amount)
FROM orders GROUP BY product_category
UNION ALL
SELECT NULL, NULL, SUM(total_amount)
FROM orders;
-- Same result as the GROUPING SETS query above, but three full scans

4. The GROUPING function: telling real NULL apart from subtotal

A central problem with GROUPING SETS, one that occurs equally with ROLLUP and CUBE, concerns distinguishing between a genuinely real NULL value in a grouping column and a NULL that the database artificially inserts to represent a subtotal row where that column was not part of the grouping. If the region column actually contains a NULL value because an order was not assigned to any region, that row looks identical in the output to a subtotal row that deliberately collapses region.

The GROUPING function resolves this ambiguity: GROUPING(column) returns 1 when the row is a subtotal row in which that column was not part of the current grouping level, and 0 when the column actually was part of the grouping, regardless of whether its value itself is NULL or not. This lets you reliably distinguish, in the SELECT list or in a downstream application, between "this region is unknown" and "this is the subtotal row across all regions", which is indispensable for correctly rendering results in a reporting tool.

-- GROUPING() disambiguates real NULL from subtotal rows
SELECT
    region,
    product_category,
    GROUPING(region)           AS is_region_subtotal,
    GROUPING(product_category) AS is_category_subtotal,
    SUM(total_amount)          AS revenue
FROM orders
GROUP BY GROUPING SETS (
    (region),
    (product_category),
    ()
)
ORDER BY region, product_category;

-- is_region_subtotal = 1 marks rows where region was collapsed,
-- not rows where region happens to be genuinely unknown

5. Distinguishing from ROLLUP and CUBE

GROUPING SETS are conceptually the most general of the three related constructs, because both ROLLUP and CUBE can be understood as shorthand for certain, fixed patterns of GROUPING SETS. ROLLUP(a, b, c) produces a hierarchical sequence of combinations, (a, b, c), (a, b), (a), (), that is, exactly n plus one combinations for n columns, fitting hierarchical data like year, quarter, month. CUBE(a, b, c), by contrast, produces all two to the power of n possible combinations of these columns, regardless of any hierarchy between them.

GROUPING SETS, on the other hand, allow a completely free selection that need not follow either ROLLUP's strict hierarchy or CUBE's complete combinatorics. With GROUPING SETS you can, for instance, combine exactly (region, product_category), (region), and () without automatically also producing (product_category) alone, which would not be technically possible with CUBE without subsequently filtering out the superfluous combination. This freedom makes GROUPING SETS the right choice whenever a report needs a combination that is neither purely hierarchical nor fully combinatorial.

6. The empty grouping for the overall grand total

The empty parenthesized group () within GROUPING SETS deserves special attention because it is often overlooked, even though nearly every report with subtotals also needs to show an overall value across the entire table. Without the empty grouping, the result contains only the individual subtotal levels but no overarching overall value, which usually shows up in a tabular report as a missing final row.

A common mistake with GROUPING SETS is accidentally omitting the empty grouping, because it is syntactically easy to overlook among the other, more obviously content-bearing combinations. Anyone who regularly builds reports with GROUPING SETS should get into the habit of including the empty grouping by default, unless a grand total is explicitly not wanted for that particular report, for example because it would not make business sense when the individual subtotals represent different units or contexts.

-- Marking the grand total row explicitly with GROUPING()
SELECT
    COALESCE(region, 'ALL REGIONS') AS region_label,
    SUM(total_amount) AS revenue,
    GROUPING(region) AS is_grand_total
FROM orders
GROUP BY GROUPING SETS (
    (region),
    ()
)
ORDER BY is_grand_total, region_label;

7. Performance: one scan for every combination

The performance advantage of GROUPING SETS over separate queries with UNION ALL follows the same logic as conditional aggregation: the database only needs to read the underlying table once, regardless of how many grouping combinations appear in the GROUPING SETS list. Modern query planners recognize GROUPING SETS as a single logical operation and typically choose a hash or sort based aggregation plan that passes over the input data once and updates all relevant grouping levels in parallel for each row.

With a very large number of different combinations, however, the memory footprint for the parallel intermediate aggregates can grow, because the planner needs to keep a separate hash table or sort structure in memory for each combination. In practice, this overhead remains substantially smaller than the cost of multiple full table scans, which is why GROUPING SETS are practically always the more performant choice over separate queries for reports with more than two or three desired subtotal combinations.

8. Common reports: crosstabs with targeted subtotals

The classic use case for GROUPING SETS is a crosstab report that needs to show revenue by region, by sales channel, and as an overall total at the same time, but explicitly does not need a fine-grained combination of region and sales channel, because that level of detail would be too confusing for the report's target audience. GROUPING SETS map exactly these three desired levels in a single query, without producing the fourth, unneeded combination.

Another typical use case concerns reports with mixed granularity, for example a financial report that shows cost centers at the top level but product lines at a finer level of detail, while a combination of cost center and product line would not make business sense, because both dimensions are organized independently of one another. GROUPING SETS allows exactly this asymmetric mix of different detail levels in a single, consistent reporting query, something that ROLLUP or CUBE alone could not directly represent.

-- Mixed granularity report: cost centers at top level, product lines at detail level
SELECT
    cost_center,
    product_line,
    SUM(amount) AS total_amount,
    GROUPING(cost_center) AS is_cost_center_subtotal,
    GROUPING(product_line) AS is_product_line_subtotal
FROM financial_entries
GROUP BY GROUPING SETS (
    (cost_center),
    (product_line),
    ()
)
ORDER BY cost_center, product_line;

9. GROUPING SETS, ROLLUP, and CUBE compared

The following overview compares the three related constructs for multi level aggregation and clarifies when GROUPING SETS is the right choice.

In practice, GROUPING SETS is the right choice as soon as a report needs neither ROLLUP's strict hierarchy nor CUBE's full combinatorics, but rather a deliberately curated subset of subtotals that exactly matches the requirements of the report's audience, without producing superfluous rows that would have to be filtered out afterward.

Applying GROUPING SETS deliberately for individually tailored subtotal combinations avoids both the cumbersomeness of multiple UNION ALL queries and the waste of a full CUBE when only a fraction of the possible combinations is actually needed from a business perspective.

10. Summary

GROUPING SETS compute exactly the desired combinations of grouping columns in a single query with a single table scan, instead of either merging several separate queries with UNION ALL or forcing every combination with CUBE and filtering out the superfluous ones afterward. Each parenthesized group in the GROUPING SETS list represents its own grouping level, the empty parenthesized group () represents the grand total across the entire table.

The GROUPING function reliably separates real NULL values in a grouping column from artificial NULL values that mark a subtotal row. Compared to ROLLUP, which produces a fixed hierarchy, and CUBE, which delivers every possible combination, GROUPING SETS offer the greatest flexibility for individually tailored reports with mixed or asymmetric levels of detail.

11. FAQ: GROUPING SETS