This post is part 1 of a 3 part series…
- Part 1 Grouping Sets
- Part 2 ROLLUP and CUBE
- Part 3 GROUPING and GROUPING_ID
Grouping Sets can be a powerful tool for reporting aggregations. Let’s imagine we have a sales table with a SaleDate and want a count of sales by month and also by year….
Given this dataset we can achieve the desired results by unioning two separate groups…
Whilst this works it quickly becomes as mass of code as you add more groups you want to aggregate by. Enter Grouping Sets, these allow you for a given query to define multiple group by clauses. For example the above query could be rewritten as….
With this solution we just need to add additional fields to the select/grouping sets as we want to aggregate by more groups.