ROLLUP Concept

ROLLUP: A Hierarchical Shortcut for GROUPING SETS

ROLLUP is a special, hierarchical case of GROUPING SETS (Topic 15.6). Instead of listing every grouping level explicitly, ROLLUP(col1, col2, ...) automatically generates a nested sequence of grouping sets, peeling off one column from the right at a time, ending in the grand total:

ROLLUP(region, product) expands to exactly GROUPING SETS ((region, product), (region), ()).

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Example

sql
SELECT region, product, SUM(amount) AS total_amount
FROM orders
GROUP BY ROLLUP(region, product)
ORDER BY region, product;

Result (10 rows: 6 detail + 3 region-subtotals + 1 grand total):

Reading this:

• The 6 detail rows are the same per-(region, product) breakdown as plain GROUP BY region, product (Topic 15.4).
• Each region gets one extra subtotal row (product = NULL) summing that region's Widget + Gadget totals — e.g. East: 160 + 150 = 310.
• One final row (region = NULL, product = NULL) is the grand total, 1060.

Notice there is no product-only subtotal (no row like region=NULL, product='Widget') — that's the key difference from CUBE (Topic 15.8). ROLLUP only rolls up along the hierarchy from right to left (region, product → region → nothing); it never produces a "skip the leftmost column" combination.

Why "Hierarchical"

ROLLUP is designed for hierarchies like (country, state, city) or (year, month, day), where each subtotal level makes natural sense: totals by city within state within country. Here, region → product isn't a "real" hierarchy (products aren't nested inside regions conceptually), so ROLLUP(region, product) still works mechanically, but reach for GROUPING SETS directly when the grouping levels you want aren't naturally nested.

Column Order Matters

Unlike plain GROUP BY, ROLLUP's column order defines its subtotal hierarchy: ROLLUP(region, product) produces region-only subtotals, while ROLLUP(product, region) would instead produce product-only subtotals (plus full detail and grand total) — a materially different result set.

Dialect Support

ROLLUP is broadly supported: PostgreSQL, SQL Server, Oracle all support the standard GROUP BY ROLLUP(...) syntax. MySQL supports only the simpler GROUP BY region, product WITH ROLLUP syntax (a trailing modifier, not the ROLLUP(...) function form), which behaves similarly but has some MySQL-specific quirks (e.g., older MySQL versions show NULL labels that can be ambiguous without GROUPING(), and it cannot be freely mixed with arbitrary GROUPING SETS).

Edge Cases

• ROLLUP(region) with a single column is equivalent to GROUPING SETS ((region), ()) — per-region totals plus a grand total, no different from adding a manual UNION ALL grand-total row.
• Combining with HAVING filters the combined output rows (including subtotal/grand-total rows), so HAVING SUM(amount) > 200 would drop the North subtotal row (200 is not > 200) while keeping East and West's subtotal rows.
• Use GROUPING(column) to reliably detect which rows are subtotals versus detail rows, exactly as with GROUPING SETS.

Key Takeaways / Interview Q&A

• Q: What does ROLLUP(region, product) expand to?

A: GROUPING SETS ((region, product), (region), ()) — detail rows, region subtotals, and a grand total.

• Q: How many rows does GROUP BY ROLLUP(region, product) return here?

A: 10 — 6 detail rows + 3 region subtotals + 1 grand total.

• Q: Does ROLLUP produce a product-only subtotal row?

A: No — that's CUBE's job, not ROLLUP's; ROLLUP only rolls up right-to-left along the given column order.

• Q: Does MySQL support GROUP BY ROLLUP(region, product)?

A: Not that exact function syntax — MySQL uses GROUP BY region, product WITH ROLLUP instead.