SQL GROUPING SETS, ROLLUP, and CUBE Explained for Subtotals and Grand Totals

Standard GROUP BY gives you one grouping level. Real reports often need detail totals, subtotals, totals by another dimension, and a grand total. GROUPING SETS, ROLLUP, and CUBE make that possible in one query.

SQL for Files uses small revenue examples so you can see exactly which rows each advanced grouping feature creates.

GROUPING SETS lists the exact group combinations you want. The empty grouping set means the grand total.

SELECT region, product, SUM(amount) AS total_amount
FROM revenue
GROUP BY GROUPING SETS ((region), (product), ());

ROLLUP is shorthand for detail rows, progressively broader subtotals, and a grand total. It is ideal when your dimensions have a natural hierarchy.

SELECT region, product, SUM(amount) AS total_amount
FROM revenue
GROUP BY ROLLUP (region, product);

CUBE produces every grouping combination for the listed columns. With two columns, that means detail rows, region totals, product totals, and the grand total.

1. Use GROUPING SETS to choose exact subtotal levels.
2. Use ROLLUP for hierarchical subtotals and a grand total.
3. Use CUBE for every dimension combination.