sql / expert
Snippet
Multi-Dimensional Aggregation with Grouping Sets
Grouping Sets allow a single query to define multiple groupings, effectively performing a UNION ALL of different GROUP BY clauses. This is highly efficient for analytical reports that require grand totals, sub-totals, and detailed breakdowns simultaneously.
snippet.sql
1
2
3
SELECT region, year, SUM(revenue)FROM sales_dataGROUP BY GROUPING SETS ((region, year), (region), ());
Breakdown
1
GROUP BY GROUPING SETS
Specifies that multiple grouping configurations will be used.
2
(region, year)
The first set: aggregates by both region and year.
3
(region)
The second set: aggregates by region only (sub-total).
4
()
The empty set: calculates the grand total for all rows.