GROUPING SETS, ROLLUP & CUBE

A single GROUP BY produces one level of grouping. But reports often need several grouping levels at once — for example, sales by region and product, sales by region alone, sales by product alone, and a single grand total row — all in one result set instead of stitching together multiple queries with UNION ALL. PostgreSQL's GROUPING SETS, ROLLUP, and CUBE exist exactly for this.

Sample data

sales

SQL

region | product  | amount
-------+----------+-------
east   | widgets  |   100
east   | gadgets  |   150
west   | widgets  |   200
west   | gadgets  |    50

GROUPING SETS — explicit combinations

GROUPING SETS lets you list exactly which groupings you want, and PostgreSQL computes all of them in a single pass over the data.

SQL

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, product),   -- by region and product
  (region),            -- by region only
  (product),           -- by product only
  ()                    -- grand total
)
ORDER BY region, product;

region | product  | total
-------+----------+------
east   | gadgets  |  150
east   | widgets  |  100
east   |          |  250
west   | gadgets  |   50
west   | widgets  |  200
west   |          |  250
       | gadgets  |  200
       | widgets  |  300
       |          |  500

Each blank cell above is a real NULL in the result — it marks a column that was rolled up rather than a group where that column genuinely had no value. More on telling the two apart below.

ROLLUP — hierarchical subtotals

ROLLUP(a, b, c) is shorthand for a specific, hierarchical set of groupings: (a, b, c), (a, b), (a), and () — it peels columns off from the right, one at a time. It's the natural fit for hierarchical data such as year > month > day, or region > product, where each level is a subtotal of the level beneath it.

SQL

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

region | product  | total
-------+----------+------
east   | gadgets  |  150
east   | widgets  |  100
east   |          |  250   <- subtotal for east
west   | gadgets  |   50
west   | widgets  |  200
west   |          |  250   <- subtotal for west
       |          |  500   <- grand total

Unlike the GROUPING SETS example above, ROLLUP(region, product) does not produce a product-only breakdown — rolling up drops columns from the right, so a per-product-across-all-regions row is never one of the levels it generates.

CUBE — every combination

CUBE(a, b) generates every possible grouping of the given columns — all subsets, in every order — which for two columns means (a, b), (a), (b), and ().

SQL

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

This produces the same nine rows as the first GROUPING SETS example — in fact, CUBE(region, product) is just a shorthand for that exact set of grouping sets.

Construct	What it generates
`GROUPING SETS (...)`	Exactly the groupings you list — full control
`ROLLUP(a, b, c)`	A hierarchy: `(a,b,c)`, `(a,b)`, `(a)`, `()`
`CUBE(a, b, c)`	All 2^n subsets of the given columns, in every combination

Distinguishing a real NULL from a subtotal row

If a column can genuinely contain NULL in the source data, a blank cell in the output becomes ambiguous — is it an actual NULL row, or is it a subtotal where that column was rolled up? The GROUPING() function resolves this: it returns 1 when the column was rolled up to produce that row, and 0 when the value is a genuine group value (including a real NULL).

SQL

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

region | product  | total | region_rolled_up | product_rolled_up
-------+----------+-------+-------------------+--------------------
east   | gadgets  |   150 |                 0 |                  0
east   | widgets  |   100 |                 0 |                  0
east   |          |   250 |                 0 |                  1
west   | gadgets  |    50 |                 0 |                  0
west   | widgets  |   200 |                 0 |                  0
west   |          |   250 |                 0 |                  1
       |          |   500 |                 1 |                  1

Note

A common pattern is to use GROUPING() together with CASE to swap the ambiguous NULL for a readable label, e.g. CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE region END.

Tip

Building a sales report with subtotals per group and one grand-total row at the bottom is the textbook use case for ROLLUP — it replaces what would otherwise be several separate queries unioned together.

GROUPING SETS lists exact groupings; ROLLUP builds a hierarchy of subtotals; CUBE generates every combination.
All three compute in a single pass over the table instead of requiring multiple UNION ALL queries.
Rolled-up columns show as NULL in the result — use GROUPING(column) to tell that apart from a real NULL value.