Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
GROUP BY – Recap
ROLLUP
Summary

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.