Q43: How do GROUPING SETS, ROLLUP, and CUBE operators work?
These extensions expand standard GROUP BY capabilities by generating multiple aggregate hierarchy sets in a single query pass.
- GROUPING SETS: Explicitly defines specific column combination sets to aggregate.
- ROLLUP: Generates hierarchical subtotal and grand total aggregations along a specified column hierarchy.
- CUBE: Generates subtotals for all possible combinations of specified dimension columns.
Generating Sales Subtotals with ROLLUP
SELECT year, region, SUM(sales_amount) AS total_sales
FROM regional_sales
GROUP BY ROLLUP (year, region);💡 Calculates sales totals aggregated by (year, region), subtotals by (year), and the grand total across all rows.
Key Takeaways
- ROLLUP builds hierarchical subtotals and grand totals.
- CUBE calculates all possible cross-tabulation aggregate combinations across listed dimensions.