Introduction
GROUP BY – Recap
ROLLUP
Instruction
Great! When using multiple columns inside ROLLUP's parentheses, it's quite easy to get lost among the resulting rows. SQL Server provides a useful function named GROUPING_ID() that tells the grouping level for a given row. Take a look:
SELECT GROUPING_ID(FullName, Category, Week) AS GroupingId, FullName, Category, Week, AVG(Score) AS AvgScore FROM ContestScore GROUP BY ROLLUP (FullName, Category, Week);
As you can see, we added GROUPING_ID() to our SELECT clause. Inside GROUPING_ID()'s parentheses, we put all the columns from ROLLUP. Let's see what the result is.
Exercise
Run the sample query.
As you can see, integer numbers appeared in the first column, denoting the grouping level for each given row. Notice that these levels are not necessarily consecutive integers. In this case, we have: 0, 1, 3, and 7. We'll explain that in a moment.
