- 10.1 Introduction
- 10.2 Grouping on One Column
- 10.3 Grouping on Two or More Columns
- 10.4 Grouping on Expressions
- 10.5 Grouping of NULL Values
- 10.6 General Rules for the GROUP BY Clause
- 10.7 Complex Examples with GROUP BY
- 10.8 Grouping with WITH ROLLUP
- 10.9 Grouping with WITH CUBE
- 10.10 Grouping Sets
- 10.11 Grouping with ROLLUP and CUBE
- 10.12 Combining Grouping Sets
- 10.13 Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.8 Grouping with WITH ROLLUP
The GROUP BY clause has many features to group data and to calculate aggregated data, such as the total number of penalties or the sum of all penalties. However, so far all statements return results in which all data is on the same level of aggregation. But what if we want to see data belonging to different aggregation levels within one statement? Imagine that we want to see in one statement the total penalty amount for each player and also the total penalty amount for all players. This is not possible with the forms of the GROUP BY clauses that we have discussed so far. For this purpose, more than two groupings within one G ROUP BY clause are required. By adding the specification WITH ROLLUP to the GROUP BY clause, it becomes possible.
Example 10.18. For each player, find the sum of all his or her penalties, plus the sum of all penalties.
A way to combine these two groupings in one statement is to use the UNION operator.
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO UNION SELECT CAST(NULL AS SIGNED INTEGER), SUM(AMOUNT) FROM PENALTIES
The result is:
PLAYERNO SUM(AMOUNT)
-------- -----------
6 100.00
8 25.00
27 175.00
44 130.00
104 50.00
? 480.00
Explanation: The rows in this intermediate result in which the PLAYERNO column is filled form the result of the first select block. The rows in which PLAYERNO is equal to NULL make up the result of the second select block. The first five rows contain data on the aggregation level of the player numbers, and the last row contains data on the aggregation level of all rows.
The specification WITH ROLLUP has been introduced to simplify this kind of statement. WITH ROLLUP can be used to ask for multiple groupings with one GROUP BY clause. The previous statement will then be:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO WITH ROLLUP
Explanation: The result of this statement is the same as the previous one. The specification WITH ROLLUP indicates that after the result has been grouped with [PLAYERNO], another grouping is needed—in this case, on all rows.
We give a formal description here. Imagine that in a GROUP BY clause, the expressions E 1, E 2, E 3, and E 4 are specified. The grouping that is performed then is [E 1, E 2, E 3, E 4]. When we add the specification WITH ROLLUP to this GROUP BY, an entire set of groupings will be performed: [E 1, E 2, E 3, E 4], [E 1, E 2, E 3], [E 1, E 2], [E 1], and finally []. The specification [] means that all rows are grouped into one group. The specified grouping is seen as the highest aggregation level that is asked, and also indicates that all higher aggregation levels must be calculated again. To aggregate upward is called rollup in literature. So, the result of this statement contains data on five different levels of aggregation.
If in the SELECT clause an expression occurs in which the result of a certain grouping is not grouped, the NULL value is placed in the result.
Example 10.19. For each combination of sex-town, get the number of players, and get the total number of players per sex and the total number of players in the entire table as well.
SELECT SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY SEX, TOWN WITH ROLLUP
The result is:
SEX TOWN COUNT(*) --- --------- -------- M Stratford 7 M Inglewood 1 M Douglas 1 M ? 9 V Midhurst 1 V Inglewood 1 V Plymouth 1 V Eltham 2 V ? 5 ? ? 14
Explanation: This result has three levels of aggregation. Rows 1, 2, 3, 5, 6, 7, and 8 form the lowest level and have been added because of the grouping [SEX, TOWN]; rows 4 and 9 have been added because of the grouping [SEX]; and the last row forms the highest level of aggregation and has been added because of the grouping []. It contains the total number of players.
Exercise 10.20: For each team, get the number of matches played and also the total number of matches.
Exercise 10.21: Group the matches by the name of the player and the division of the team, and execute a ROLLUP.