SQL 62 🛢️ Grouping Sets: ROLLUP, CUBE, and GROUPING SETS - KandZ

A GROUP BY clause with a single set of columns produces one row per distinct combination of those columns. That is the standard aggregation behavior, and it answers a specific question: “what is the total for each group?” But analytical queries often need more than one answer at once. A report might need the total for each region, the total for each product, and the grand total — all in the same result set. Writing three separate queries and combining them with UNION ALL works, but it is verbose, and the database scans the table three times. SQL’s grouping sets feature solves this by letting a single query specify multiple grouping combinations, and the database computes them all in one pass.

The three constructs are ROLLUP, CUBE, and GROUPING SETS. They are related but not identical. ROLLUP produces a hierarchical set of groupings: the full combination, then progressively coarser combinations, ending with the grand total. CUBE produces every possible combination of the specified columns, including the grand total and every subset. GROUPING SETS is the most general: it lets you specify an arbitrary list of grouping combinations, and the other two are shorthand for common patterns. Every ROLLUP and every CUBE can be expressed as a GROUPING SETS list, but not every GROUPING SETS list can be expressed as a ROLLUP or CUBE .

The result set includes rows for the subtotals and grand totals, and these rows have NULL in the columns that are not part of the grouping. The NULL is ambiguous: it could mean “this is a subtotal” or “this row’s value is actually NULL.” The GROUPING() function resolves the ambiguity by returning 1 for a column that is aggregated away and 0 for a column that is part of the grouping. The related GROUPING_ID() function returns a bitmask that identifies which grouping set a row belongs to. These functions are essential for interpreting the output and for ordering the rows so the subtotals appear in the right place .

This chapter covers three areas. First, why grouping sets exist — the problem of multiple aggregation levels in one result set, and the cost of the UNION ALL alternative. Second, how ROLLUP, CUBE, and GROUPING SETS work — the syntax, the combinations each produces, and the order of the rows. Third, how to interpret the output — the GROUPING() and GROUPING_ID() functions, the meaning of the NULL values, and the patterns for filtering and ordering the results. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the grouping combinations.

Key point: ROLLUP produces hierarchical subtotals from the finest to the grand total. CUBE produces every combination of the specified columns. GROUPING SETS lets you specify an arbitrary list of combinations. All three are computed in a single pass, and all three produce NULL in the columns that are aggregated away. The GROUPING() function distinguishes a subtotal NULL from a data NULL .

Why grouping sets exist

The multiple-levels problem. A single GROUP BY produces one level of aggregation. A report that needs a detail level and a summary level is two queries. A report that needs three levels is three queries. Each query scans the table, and the results must be combined. The UNION ALL approach works, but it repeats the FROM clause, the WHERE clause, and the aggregate expressions. A change to the filter must be applied to every query in the union, and a change to an aggregate must be applied everywhere. The repetition is error-prone and verbose .

The performance problem. A UNION ALL of three GROUP BY queries scans the base table three times. Each scan reads the same rows and computes overlapping aggregates. The database cannot share work between the branches of the union because they are separate queries. Grouping sets solve this by computing all the groupings in a single pass over the data. The database reads the table once, computes the aggregates for each grouping set, and produces the combined result. On large tables, the difference is significant .

The interpretation problem. The result of a grouping sets query contains rows that represent different levels of aggregation. A row with a value in the region column and a value in the product column is a detail row. A row with a value in the region column and NULL in the product column is a region subtotal. A row with NULL in both is the grand total. The NULL values are the markers of the aggregation level, and the GROUPING() function makes the markers explicit. Without GROUPING(), a NULL in the product column could mean “this is a region subtotal” or “this row’s product is unknown.” The function distinguishes the two .

The hierarchy problem. Some data has a natural hierarchy: year, quarter, month, day. A report that shows the total for each level of the hierarchy is a ROLLUP query. The ROLLUP produces the full detail, then the next level up, then the next, ending with the grand total. The order of the columns in the ROLLUP determines the hierarchy. ROLLUP(year, quarter, month) produces groupings for (year, quarter, month), (year, quarter), (year), and (). ROLLUP(month, year) produces (month, year), (month), and () — the hierarchy is reversed .

The cross-tabulation problem. A report that shows totals for every combination of two dimensions is a CUBE query. The CUBE produces the full cross-tabulation, plus the subtotals for each dimension, plus the grand total. A query with CUBE(region, product) produces groupings for (region, product), (region), (product), and (). This is the standard pattern for a pivot-table report where the rows and columns are both dimensions, and the cells are aggregates .

The trade-off. Grouping sets are more complex than a simple GROUP BY. The result set contains rows at different levels of aggregation, and the NULL values require interpretation. The GROUPING() function is essential, and it must be used correctly to distinguish subtotal NULL from data NULL. The syntax varies slightly between databases, and not every database supports every construct. The trade-off is between the convenience of a single query and the complexity of interpreting a multi-level result. For analytical reports, the convenience usually wins.

a. ROLLUP — hierarchical subtotals

ROLLUP produces a result set with the full grouping, then progressively coarser groupings, ending with the grand total. The order of the columns determines the hierarchy .

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

The groupings produced are:

(region, product) — the full detail.
(region) — the subtotal for each region.
() — the grand total.

The result set has a row for each detail combination, a row for each region, and one row for the grand total. The rows where product is NULL are the region subtotals. The row where both are NULL is the grand total .

ROLLUP can be combined with other columns in the GROUP BY:

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

The year column is a regular grouping column, and the ROLLUP applies to region and product. The groupings are (year, region, product), (year, region), (year), and (year) — wait, the last one is (year) because year is a regular column and is always present. The grand total is not produced because year is not part of the rollup. This is a common point of confusion: ROLLUP produces the grand total only when all the grouping columns are inside the rollup .

The order of the columns in the ROLLUP matters. ROLLUP(a, b, c) produces (a, b, c), (a, b), (a), and (). ROLLUP(c, b, a) produces (c, b, a), (c, b), (c), and (). The hierarchy is from the first column to the last, and the grand total is always the last grouping.

b. CUBE — all combinations

CUBE produces every possible combination of the specified columns. For n columns, it produces 2^n groupings, including the full combination and the empty set .

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

The groupings produced are:

(region, product) — the full detail.
(region) — the subtotal for each region.
(product) — the subtotal for each product.
() — the grand total.

The result set has a row for each detail combination, a row for each region, a row for each product, and one row for the grand total. The rows where region is NULL and product is not are the product subtotals. The rows where product is NULL and region is not are the region subtotals. The row where both are NULL is the grand total .

CUBE is the right choice when the report needs subtotals for every dimension, not just the hierarchical ones. A cross-tabulation of two dimensions is a CUBE query. A report that needs the total by region, the total by product, and the total by region and product is a CUBE query .

For three columns, CUBE produces eight groupings: the full combination, three two-column combinations, three one-column combinations, and the grand total. The number of groupings grows exponentially with the number of columns, so CUBE is usually limited to two or three columns.

c. GROUPING SETS — arbitrary combinations

GROUPING SETS is the most general construct. It takes a list of grouping combinations, and the query produces the union of all of them .

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

This produces the same result as CUBE(region, product). The difference is that GROUPING SETS lets you specify exactly which combinations you want, without producing the ones you do not need. A query that needs the detail and the region subtotal, but not the product subtotal or the grand total, is:

GROUP BY GROUPING SETS (
  (region, product),
  (region)
)

This is more efficient than a CUBE that computes all four groupings and then filters out the ones that are not needed. GROUPING SETS is the tool for precise control over which aggregation levels are produced.

GROUPING SETS can mix columns and other constructs:

GROUP BY GROUPING SETS (
  (region, product),
  ROLLUP(year, quarter),
  ()
)

This combines an explicit grouping set, a rollup, and the grand total. The flexibility is what makes GROUPING SETS the most general of the three constructs. ROLLUP and CUBE are shorthand for common GROUPING SETS patterns .

The order of the rows in the result set is not guaranteed unless ORDER BY is used. The GROUPING() function can be used in the ORDER BY clause to sort the subtotals and grand totals into a predictable position.

Complete Example Session

-- ============================================
-- PART 1: THE SAMPLE TABLE
-- ============================================

CREATE TABLE sales (
  id INT,
  year INT,
  quarter INT,
  region TEXT,
  product TEXT,
  amount NUMERIC
);

INSERT INTO sales VALUES
  (1, 2024, 1, 'North', 'Widget', 100),
  (2, 2024, 1, 'North', 'Gadget', 150),
  (3, 2024, 1, 'South', 'Widget', 200),
  (4, 2024, 2, 'North', 'Widget', 50),
  (5, 2024, 2, 'South', 'Gadget', 300),
  (6, 2024, 2, 'South', 'Widget', 100);


-- ============================================
-- PART 2: A BASIC GROUP BY
-- ============================================

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

-- One row per region-product combination.
-- No subtotals, no grand total.


-- ============================================
-- PART 3: ROLLUP ON REGION AND PRODUCT
-- ============================================

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

-- Groupings:
--   (region, product) — detail
--   (region)          — region subtotal
--   ()                — grand total


-- ============================================
-- PART 4: CUBE ON REGION AND PRODUCT
-- ============================================

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

-- Groupings:
--   (region, product) — detail
--   (region)          — region subtotal
--   (product)         — product subtotal
--   ()                — grand total


-- ============================================
-- PART 5: GROUPING SETS FOR PRECISE CONTROL
-- ============================================

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

-- Groupings:
--   (region, product) — detail
--   (region)          — region subtotal
-- No product subtotal, no grand total.


-- ============================================
-- PART 6: THE GROUPING() FUNCTION
-- ============================================

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

-- GROUPING(region) = 1 when region is aggregated away (grand total)
-- GROUPING(product) = 1 when product is aggregated away (region subtotal)
-- GROUPING() distinguishes subtotal NULL from data NULL.


-- ============================================
-- PART 7: ROLLUP WITH A REGULAR COLUMN
-- ============================================

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

-- Groupings:
--   (year, region, product)
--   (year, region)
--   (year)
-- The grand total is NOT produced because year is
-- a regular grouping column, not part of the rollup.


-- ============================================
-- PART 8: GROUPING_ID() FOR ROW IDENTIFICATION
-- ============================================

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

-- GROUPING_ID returns a bitmask:
--   0 = (region, product) detail
--   1 = (region) subtotal
--   2 = (product) subtotal
--   3 = () grand total


-- ============================================
-- PART 9: ORDERING WITH GROUPING()
-- ============================================

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

-- GROUPING() in ORDER BY sorts the detail rows first,
-- then the region subtotals, then the grand total.


-- ============================================
-- PART 10: THE COMPLETE REPORT
-- ============================================

SELECT
  COALESCE(region, 'ALL REGIONS') AS region,
  COALESCE(product, 'ALL PRODUCTS') AS product,
  SUM(amount) AS total,
  GROUPING_ID(region, product) AS level
FROM sales
GROUP BY CUBE(region, product)
ORDER BY level, region, product;

-- COALESCE replaces the NULL markers with readable labels.
-- GROUPING_ID identifies the level of each row.
-- The result is a complete cross-tabulation report.

The ten parts show the sample table, a basic GROUP BY, ROLLUP, CUBE, GROUPING SETS, the GROUPING() function, ROLLUP with a regular column, GROUPING_ID(), ordering with GROUPING(), and the complete report.

Quick Reference

The Three Constructs

Construct	Groupings produced	Use case
`ROLLUP(a, b)`	`(a, b)`, `(a)`, `()`	Hierarchical subtotals
`CUBE(a, b)`	`(a, b)`, `(a)`, `(b)`, `()`	All combinations
`GROUPING SETS`	As specified	Arbitrary combinations

Syntax

Construct	Syntax
ROLLUP	`GROUP BY ROLLUP(a, b, c)`
CUBE	`GROUP BY CUBE(a, b, c)`
GROUPING SETS	`GROUP BY GROUPING SETS ((a, b), (a), ())`
Mixed	`GROUP BY a, ROLLUP(b, c)`

GROUPING() Function

Return value	Meaning
`0`	Column is part of the grouping (not aggregated away)
`1`	Column is aggregated away (subtotal or grand total)

GROUPING_ID() Function

Grouping	GROUPING_ID(a, b)
`(a, b)`	0
`(a)`	1
`(b)`	2
`()`	3

The value is a bitmask: the leftmost column is the most significant bit.

Number of Groupings

Construct	Columns	Groupings
ROLLUP	n	n + 1
CUBE	n	2^n
GROUPING SETS	as specified	as specified

Best Practices

✅ Do This:

-- Use ROLLUP for hierarchical subtotals
GROUP BY ROLLUP(year, quarter, month)                                     -- ✅
-- Use CUBE for cross-tabulation
GROUP BY CUBE(region, product)                                            -- ✅
-- Use GROUPING SETS for precise control
GROUP BY GROUPING SETS ((region, product), (region))                      -- ✅
-- Use GROUPING() to distinguish subtotal NULL from data NULL
SELECT GROUPING(region) AS is_subtotal                                    -- ✅
-- Use GROUPING() in ORDER BY for predictable ordering
ORDER BY GROUPING(region), region                                         -- ✅
-- Use COALESCE for readable labels
SELECT COALESCE(region, 'ALL') AS region                                  -- ✅

❌ Don’t Do This:

-- Don't assume ROLLUP produces the grand total with a regular column
GROUP BY year, ROLLUP(region)  -- no grand total                          -- ❌
-- Don't use CUBE with many columns
GROUP BY CUBE(a, b, c, d, e)  -- 32 groupings                             -- ❌
-- Don't forget GROUPING() when NULL is ambiguous
SELECT region, SUM(amount) FROM sales GROUP BY ROLLUP(region)             -- ❌
-- Don't rely on row order without ORDER BY
GROUP BY ROLLUP(region)  -- rows may be in any order                      -- ❌
-- Don't use UNION ALL when GROUPING SETS works
SELECT ... UNION ALL SELECT ... UNION ALL SELECT ...                      -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
Grand total missing	Regular column outside ROLLUP	Put all columns inside ROLLUP
CUBE produces too many rows	Exponential growth	Use GROUPING SETS with only the needed combinations
Subtotal NULL confused with data NULL	No GROUPING() function	Add `GROUPING(column)`
Rows in unexpected order	No ORDER BY	Add `ORDER BY GROUPING(...)`
ROLLUP hierarchy wrong	Column order reversed	Put the most detailed column first
Aggregates wrong at subtotal level	Filter in WHERE removes rows	Filter in HAVING after grouping
Database does not support a construct	Version or vendor	Use GROUPING SETS as fallback

Real-World Examples

1. ROLLUP on Two Columns

GROUP BY ROLLUP(region, product)

2. CUBE on Two Columns

GROUP BY CUBE(region, product)

3. GROUPING SETS with Two Combinations

GROUP BY GROUPING SETS ((region, product), (region))

4. GROUPING() to Identify Subtotals

SELECT GROUPING(region) AS is_region_subtotal

5. GROUPING_ID() for Level Identification

SELECT GROUPING_ID(region, product) AS level

6. COALESCE for Readable Labels

SELECT COALESCE(region, 'ALL REGIONS') AS region

7. ROLLUP on a Hierarchy

GROUP BY ROLLUP(year, quarter, month)

8. CUBE for Cross-Tabulation

GROUP BY CUBE(region, product)

9. Ordering Subtotals Last

ORDER BY GROUPING(region), region

10. Mixed Grouping Sets

GROUP BY GROUPING SETS ((region, product), ROLLUP(year, quarter), ())

Visual

ROLLUP vs CUBE

┌──────────────────────────────────────────────────────────────┐
│  ROLLUP vs CUBE                                              │
│                                                              │
│  ROLLUP(region, product):                                    │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  (region, product)  ← detail                         │    │
│  │  (region)           ← region subtotal                │    │
│  │  ()                 ← grand total                    │    │
│  │                                                       │    │
│  │  3 groupings                                         │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  CUBE(region, product):                                      │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  (region, product)  ← detail                         │    │
│  │  (region)           ← region subtotal                │    │
│  │  (product)          ← product subtotal               │    │
│  │  ()                 ← grand total                    │    │
│  │                                                       │    │
│  │  4 groupings                                         │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  CUBE adds the product subtotal that ROLLUP does not have.   │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The GROUPING() Function

┌──────────────────────────────────────────────────────────────┐
│  THE GROUPING() FUNCTION                                     │
│                                                              │
│  Result of ROLLUP(region, product):                          │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  region  product  total  GROUPING(region) GROUPING(product)
│  │  ──────  ───────  ─────  ──────────────── ────────────────
│  │  North   Widget   100    0                0          │    │
│  │  North   Gadget   150    0                0          │    │
│  │  South   Widget   300    0                0          │    │
│  │  North   NULL     250    0                1  ← region subtotal
│  │  South   NULL     300    0                1  ← region subtotal
│  │  NULL    NULL     550    1                1  ← grand total
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  GROUPING(column) = 1 means the column is aggregated away.   │
│  GROUPING(column) = 0 means the column is part of the group. │
│                                                              │
│  Without GROUPING(), the NULL in product could mean          │
│  "subtotal" or "unknown product."                            │
│                                                              │
└──────────────────────────────────────────────────────────────┘

GROUPING_ID() Bitmask

┌──────────────────────────────────────────────────────────────┐
│  GROUPING_ID() BITMASK                                       │
│                                                              │
│  GROUPING_ID(region, product):                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  (region, product)  →  0  (00 binary)                │    │
│  │  (region)           →  1  (01 binary)                │    │
│  │  (product)          →  2  (10 binary)                │    │
│  │  ()                 →  3  (11 binary)                │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The leftmost column is the most significant bit.            │
│  A set bit means the column is aggregated away.              │
│                                                              │
│  GROUPING_ID can be used in ORDER BY to sort by level:       │
│  ORDER BY GROUPING_ID(region, product)                       │
│  → detail first, then region subtotals, then product         │
│    subtotals, then grand total                               │
│                                                              │
└──────────────────────────────────────────────────────────────┘

GROUPING SETS Combinations

┌──────────────────────────────────────────────────────────────┐
│  GROUPING SETS COMBINATIONS                                  │
│                                                              │
│  GROUPING SETS (                                             │
│    (region, product),   ← detail                             │
│    (region),            ← region subtotal                    │
│    (product),           ← product subtotal                   │
│    ()                   ← grand total                        │
│  )                                                           │
│                                                              │
│  This is equivalent to CUBE(region, product).                │
│                                                              │
│  GROUPING SETS (                                             │
│    (region, product),   ← detail                             │
│    (region)             ← region subtotal only               │
│  )                                                           │
│                                                              │
│  This produces only two groupings, not four.                 │
│  It is more efficient than CUBE when only two are needed.    │
│                                                              │
│  GROUPING SETS is the most general construct.                │
│  ROLLUP and CUBE are shorthand for common patterns.          │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
`ROLLUP`	Hierarchical subtotals: `(a, b)`, `(a)`, `()`
`CUBE`	All combinations: `(a, b)`, `(a)`, `(b)`, `()`
`GROUPING SETS`	Arbitrary list of combinations
`GROUPING(column)`	1 if aggregated away, 0 if part of grouping
`GROUPING_ID(...)`	Bitmask identifying the grouping set
NULL in result	Marker for aggregated-away column
Grand total	The `()` grouping
Number of groupings	ROLLUP: n+1; CUBE: 2^n
Order of rows	Not guaranteed without `ORDER BY`

Key takeaways:

ROLLUP produces hierarchical subtotals. The order of the columns determines the hierarchy. ROLLUP(a, b) produces (a, b), (a), and (). The grand total is produced only when all grouping columns are inside the rollup .
CUBE produces every combination. For n columns, it produces 2^n groupings, including the full combination, every subset, and the grand total. It is the right choice for cross-tabulation, but the number of groupings grows exponentially .
GROUPING SETS is the most general. It lets you specify exactly which combinations you want. ROLLUP and CUBE are shorthand for common GROUPING SETS patterns. Use it when you need precise control over which aggregation levels are produced .
The result set contains rows at multiple levels. The rows where a column is NULL are the subtotals and grand totals. The NULL is a marker, not a data value .
The GROUPING() function distinguishes subtotal NULL from data NULL. It returns 1 for a column that is aggregated away and 0 for a column that is part of the grouping. This is essential for interpreting the output and for ordering the rows .
GROUPING_ID() returns a bitmask. The leftmost column is the most significant bit. The value identifies which grouping set a row belongs to, and it can be used in ORDER BY to sort the rows by level .
The rows are not ordered by default. The ORDER BY clause is required for predictable output. The GROUPING() function can be used in ORDER BY to sort the subtotals and grand totals into a specific position .
Grouping sets are more efficient than UNION ALL. A single query with grouping sets scans the table once, while a UNION ALL of multiple GROUP BY queries scans it multiple times. On large tables, the difference is significant .

Remember: Grouping sets let a single query produce multiple levels of aggregation. ROLLUP produces hierarchical subtotals, CUBE produces every combination, and GROUPING SETS produces an arbitrary list. All three are computed in one pass, and all three produce NULL in the columns that are aggregated away. The GROUPING() function distinguishes a subtotal NULL from a data NULL, and the GROUPING_ID() function identifies which grouping set a row belongs to. The result set contains rows at different levels, and the ORDER BY clause is required for predictable output. Grouping sets are the standard tool for analytical reports that need detail and summary in the same result set.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!