GROUPING SETS, ROLLUP, CUBE — SQL

GROUPING SETS, ROLLUP, and CUBE

A single GROUP BY gives you one level of aggregation — say, sales per region. But reports often need several levels of subtotal at once: sales per region, sales per product, and a grand total, all in one result set. Rather than running several separate queries and combining them with UNION, SQL provides GROUPING SETS, ROLLUP, and CUBE to compute multiple grouping levels in a single pass.

Sample data

region	product	amount
East	Widget	100
East	Gadget	150
West	Widget	200
West	Gadget	50

GROUPING SETS — pick exactly which groupings you want

GROUPING SETS lets you list several groupings explicitly, and the database computes each one and unions the results together:

Subtotals by region, by product, and a grand total

SQL

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, product),  -- detail rows
  (region),            -- subtotal per region
  (product),           -- subtotal per product
  ()                   -- grand total across everything
);

 region | product | total
--------+---------+-------
 East   | Widget  |   100
 East   | Gadget  |   150
 West   | Widget  |   200
 West   | Gadget  |    50
 East   | NULL    |   250
 West   | NULL    |   250
 NULL   | Widget  |   300
 NULL   | Gadget  |   200
 NULL   | NULL    |   500

NULL in a grouping column here means “this row is a subtotal across all values of that column,” not a real missing value.

ROLLUP — hierarchical subtotals

ROLLUP is a shorthand for a common pattern: subtotals that build up a hierarchy from most detailed to a grand total. It assumes the columns you list have a parent-child relationship (region contains products, in this example):

ROLLUP produces a running hierarchy of subtotals

SQL

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);

This produces: one row per (region, product) combination, one subtotal row per region (product = NULL), and one grand total row (both NULL) — but unlike the GROUPING SETS example above, it does not produce a subtotal broken down by product alone, since that isn’t part of the region-then-product hierarchy.

CUBE — every possible combination

CUBE goes further than ROLLUP: it computes subtotals for every combination of the listed columns, not just the hierarchical ones:

CUBE produces all combinations of subtotals

SQL

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

For two columns, CUBE produces the same result as the GROUPING SETS example above: detail rows, subtotals by region, subtotals by product, and a grand total.

Choosing between them

Tool	Use when...
GROUPING SETS	You want precise control over exactly which groupings appear
ROLLUP	Your columns form a natural hierarchy and you want cumulative subtotals up to a grand total
CUBE	You want subtotals for every possible combination of the listed columns

Dialect support varies

PostgreSQL, SQL Server, and Oracle all support GROUPING SETS, ROLLUP, and CUBE directly. MySQL supports only WITH ROLLUP (a slightly different, simpler syntax appended after GROUP BY) and does not support GROUPING SETS or CUBE natively — the same result can usually be built there with UNION ALL across separate GROUP BY queries.

GROUPING SETS, ROLLUP, and CUBE compute multiple aggregation levels in a single query instead of several UNIONed queries
NULL in a grouping column of the result marks a subtotal row, not a missing value
The GROUPING() function can distinguish a "real" NULL from a subtotal-marker NULL when you need to label rows in a report