Introduction
GROUP BY – Recap
ROLLUP
Instruction
When using multiple columns inside ROLLUP's parentheses, it's quite easy to get lost among the resulting rows. SQL offers a function that tells you if the column is included in the grouping: GROUPING().
The GROUPING() function takes one column as an argument. It returns a 0 if the column is used in the grouping and a 1 if it is not. Take a look:
SELECT full_name, category, week, AVG(score) AS avg_score, GROUPING(full_name) AS F, GROUPING(category) AS C, GROUPING(week) AS W FROM contest_score GROUP BY ROLLUP (full_name, category, week);
As you can see, we added three GROUPING() functions in our SELECT clause. Inside the parentheses, we put all columns from the parentheses of ROLLUP. Let's see what the result is.
Exercise
Run the template query. As you can see, integer numbers appear in the final columns, denoting the grouping level for a given row.
