Introduction
GROUP BY – Recap
ROLLUP
Summary

Instruction

Good! Now we know how well each contestant did in general (GROUP BY FullName), and also how well they did in each category (GROUP BY FullName, Category). But note one thing: by adding the second column to the GROUP BY clause, we lost some information from the previous query. With the GROUP BY FullName, Category clause, we know the average scores for each contestant in each category, but we are no longer able to check the overall average for each contestant.

That's where GROUP BY ROLLUP comes in handy. Take a look:

SELECT
 FullName,
 Category, 
 AVG(Score) AS AvgScore
FROM ContestScore
GROUP BY ROLLUP (FullName, Category);

Note the change in the GROUP BY clause. We added the ROLLUP operator, followed by a pair of parentheses. Inside, we put FullName and Category.

Let's see what changes when we use ROLLUP.

Exercise

Run the template query and note what happens.

Apart from averages by contestant and category, we can also see averages by contestant and an overall average across all contestants and categories.