Introduction
GROUP BY – Recap
ROLLUP
Summary

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.