What does GROUP BY ROLLUP do?
ROLLUP adds subtotal and grand-total rows to a grouped result. For GROUP BY ROLLUP(a, b) you get rows for (a, b), then a subtotal per a, then one grand total, with NULL marking the rolled-up columns.
Explain it simply
A shop owner wants a sales sheet by city and product, with a subtotal line after each city and a grand total at the bottom. ROLLUP produces those extra lines automatically instead of running several queries and stacking them.
Example
Using the sales data of Pune-Tea 250, Pune-Coffee 200, Delhi-Tea 80:
-- PostgreSQL, Oracle, SQL Server
SELECT city, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (city, product);
-- MySQL: GROUP BY city, product WITH ROLLUP| city | product | total |
|---|---|---|
| Delhi | Tea | 80 |
| Delhi | NULL | 80 |
| Pune | Coffee | 200 |
| Pune | Tea | 250 |
| Pune | NULL | 450 |
| NULL | NULL | 530 |
The NULLs mean "all products" or "all cities". If your real data also has NULLs, use GROUPING(city), which returns 1 on a rollup row, to tell them apart.
The hierarchy matters: ROLLUP(city, product) gives subtotals by city, not by product. CUBE gives subtotals for every combination.
What to say in the interview
"ROLLUP gives hierarchical subtotals and a grand total in one pass. NULL in a column marks a rolled-up level, and GROUPING() distinguishes it from real NULLs."