Detailed explanation
GROUP BY with ROLLUP extends ordinary grouping by adding subtotal and grand-total rows to the same result. A normal GROUP BY region, city returns one aggregate row for each distinct region–city combination. GROUP BY ROLLUP (region, city) also returns one subtotal for each region and one grand total across all sales. This is useful for hierarchical reports, such as city sales rolled up into regional totals and then an overall total, without writing separate aggregate queries and combining them manually.
For example, SELECT region, city, SUM(total) AS sales_total FROM sales GROUP BY ROLLUP (region, city); requests three grouping levels: (region, city), (region), and (). The empty grouping set represents the grand total. Conceptually, this is equivalent to GROUP BY GROUPING SETS ((region, city), (region), ()). ROLLUP removes grouping expressions from right to left, so column order defines the hierarchy. Reversing the expressions produces city subtotals instead of region subtotals. For n grouping expressions, ROLLUP generates n + 1 grouping sets, not every possible combination.
Columns excluded from a grouping level appear as NULL in that level’s output. A region subtotal therefore has NULL for city, while the grand total has NULL for both region and city. However, source data can also contain genuine NULL values. GROUPING(city) distinguishes them: it returns 1 when city was excluded to create an aggregate row, and 0 when city participated in grouping, even if its value is NULL. Use CASE with GROUPING to label totals safely rather than relying only on COALESCE.
ROLLUP describes the required aggregation levels; the database chooses the physical execution strategy, which may involve sorting, hashing, or other optimisations. It does not guarantee that detail rows appear before subtotals: use an explicit ORDER BY when presentation order matters. Interviewers typically expect you to explain the hierarchy, identify generated totals, and handle NULL ambiguity. Syntax is dialect-specific: PostgreSQL and SQL Server support GROUP BY ROLLUP (region, city), while MySQL commonly uses GROUP BY region, city WITH ROLLUP.
Key points to remember
- ROLLUP returns ordinary grouped aggregates, hierarchical subtotals, and a grand total.
- ROLLUP(region, city) generates grouping sets (region, city), (region), and ().
- Expression order matters because ROLLUP removes expressions from right to left.
- GROUPING(column) distinguishes subtotal placeholders from genuine NULL values.
- ROLLUP does not guarantee output order; use ORDER BY for report presentation.
Common mistakes candidates make
- Expecting ROLLUP(region, city) to produce independent city-only totals as well as region totals.
- Using IS NULL or COALESCE alone to identify subtotal rows when source columns can contain NULL.
- Assuming ROLLUP syntax and default result ordering are identical across database systems.
Likely follow-up questions
How does ROLLUP differ from CUBE?
ROLLUP generates a hierarchy of grouping sets, while CUBE generates every combination of the grouping expressions. For region and city, CUBE additionally includes the city-only grouping set.
How can you label subtotal and grand-total rows?
Use CASE WHEN GROUPING(city) = 1 THEN 'All cities' ELSE city END to label rolled-up city values. Apply the same approach to region; unlike COALESCE, this preserves the distinction between generated totals and genuine NULL values.
How do WHERE and HAVING affect a ROLLUP query?
WHERE filters source rows before aggregation, so excluded sales do not contribute to any total. HAVING filters the generated aggregate rows and can use GROUPING expressions to retain or remove particular aggregation levels.
Interview tip: Explain the three grouping levels in the example first, then mention column order and GROUPING() to demonstrate practical understanding.