GROUPING SETS asks one SQL statement to produce several aggregation levels. A sales report can return totals by region and product, region subtotals, and a grand total together. The result contains different kinds of rows, so identifying each row’s level is as important as calculating its amount correctly.

Declare the report’s levels

GROUPING SETS ((region,product),(region),()) specifies three groupings. The first yields one row for each observed region-product combination, the second one per region, and the empty set aggregates all selected input rows. This is a declaration of result semantics; the optimizer chooses how to execute it. It can share work across levels, but the syntax does not guarantee a particular number of physical scans or eliminate sorting and memory costs.

The shorthand ROLLUP(region,product) expands to these same prefixes, including the empty set. Reversing the columns changes the subtotal dimension. CUBE(region,product) also includes product-only totals, covering all subsets of its dimensions. Six independent dimensions mean 64 grouping sets, not merely 64 output rows: each set can contain many distinct groups, so output size may grow much more than the number of levels suggests.

All levels aggregate the population selected by FROM and WHERE. A filter restricting sales to one month also restricts the grand total to that month. If the report needs different populations at different levels, a single shared input with these sets may not express that requirement. Define the population before comparing totals with another report.

Label levels with GROUPING

Columns omitted from a grouping set appear as SQL NULL in its output rows. Real input values may also be NULL, so inspecting null columns cannot reliably identify a subtotal. GROUPING(region,product) supplies a bitmask that describes which grouping expressions are absent from the current set. The rightmost argument contributes the least significant bit.

For this report, zero means both expressions participate, one means product is omitted, and three means both are omitted. A region subtotal for an actual null region and the grand total can therefore have identical null dimension columns but different masks. Use that mask in the data contract and derive labels such as “all regions” only when the level indicates aggregation.

A grouping expression’s presence is different from its value being nonnull. GROUPING reports the former. Its arguments must match grouping expressions from the query; it is not an ordinary function for testing arbitrary values. Give downstream consumers the numeric level or another unambiguous discriminator instead of replacing every null with a subtotal label.

When it breaks

Adding every returned total together counts the same sales multiple times, once at each included level. Select a single level when computing another summary, or use the already calculated grand total. Averages and distinct counts need even more care: averaging region averages without their weights does not generally reproduce the overall average, and overlapping distinct sets cannot simply be added.

The empty grouping set still emits a row when its input is empty. Count returns zero, while sum returns NULL, so a blank amount is not evidence that the query failed to return a grand-total row. Decide how the report represents an empty population. Also avoid accidentally repeating grouping sets; duplicate level specifications can produce repeated aggregate rows rather than extra independent evidence.

Check it quickly

Save this as grouping.sql and run psql -X -v ON_ERROR_STOP=1 -f grouping.sql in a disposable PostgreSQL 18 database. The fixture deliberately includes a real null region and uses a transaction to remove its temporary objects afterward.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE sales (region text, product text, amount integer);
INSERT INTO sales VALUES ('east','A',10),('east','B',20),(NULL,'A',5);
CREATE TEMP VIEW levels AS
SELECT region,product,sum(amount) AS total,GROUPING(region,product) AS level
FROM sales GROUP BY GROUPING SETS ((region,product),(region),());
SELECT * FROM levels ORDER BY level,region NULLS LAST,product NULLS LAST;
SELECT sum(total) AS incorrect_retotal,
       sum(total) FILTER (WHERE level=0) AS detail_total FROM levels;
CREATE TEMP VIEW empty_level AS
SELECT count(*) AS n,sum(amount) AS total FROM sales WHERE false GROUP BY GROUPING SETS (());
SELECT * FROM empty_level;
DO $$ BEGIN
  IF (SELECT count(*) FROM levels) <> 6 THEN RAISE EXCEPTION 'wrong level count'; END IF;
  IF (SELECT total FROM levels WHERE level=3) IS DISTINCT FROM 35 THEN
    RAISE EXCEPTION 'wrong grand total';
  END IF;
  IF (SELECT total FROM levels WHERE level=1 AND region IS NULL) IS DISTINCT FROM 5 THEN
    RAISE EXCEPTION 'real null region subtotal lost';
  END IF;
  IF (SELECT count(*) FROM levels WHERE region IS NULL AND product IS NULL) <> 2 THEN
    RAISE EXCEPTION 'test did not include ambiguous NULL labels';
  END IF;
  IF (SELECT n FROM empty_level) IS DISTINCT FROM 0::bigint OR
     (SELECT total FROM empty_level) IS NOT NULL THEN
    RAISE EXCEPTION 'wrong empty-input aggregate';
  END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 returned six rows: three detailed groups, two region subtotals, and one grand total. The east subtotal was 30. The null-region subtotal was 5 with mask one, while the grand total was 35 with mask three. Both latter rows had null region and product columns, proving why null-based labeling is ambiguous.

The incorrect re-total was 105, whereas summing just the detailed groups returned 35. The empty-input query returned one row with count zero and a null sum. Assertions check the level count, grand total, real-null subtotal, and empty-input behavior. Final ORDER BY controls presentation explicitly; GROUPING SETS itself does not promise that subtotals arrive next to their detail rows.

Sources