- 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.9 Grouping with WITH CUBE
Another way to get multiple groupings within one GROUP BY clause is to use the WITH CUBE specification.
Again, we use a formal way to explain this new specification. Imagine that the specification WITH CUBE is added to a GROUP BY clause consisting of the expressions E 1, E 2, and E 3. As a result, many groupings are performed: [E 1, E 2, E 3], [E 1, E 2], [E 1, E 3], [E 2, E 3], [E 1], [E 2], [E 3], and finally []. The list begins with a grouping on all three expressions, followed by three groupings with each two expressions (one grouping for each possible combination of two expressions), and followed by a grouping for each expression separately; it closes with a grouping of all rows.
Example 10.20. Group the PLAYERS table on the columns SEX with TOWN and add a WITH CUBE specification.
SELECT ROW_NUMBER() OVER () AS SEQNO,
SEX, TOWN, COUNT(*)
FROM PLAYERS
GROUP BY SEX, TOWN WITH CUBE
ORDER BY SEX, TOWN
The result is:
SEQNO SEX TOWN COUNT(*)
----- --- --------- --------
1 M Stratford 7
2 M Inglewood 1
3 M Douglas 1
4 M ? 9
5 F Midhurst 1
6 F Inglewood 1
7 F Plymouth 1
8 F Eltham 2
9 F ? 5
10 ? Stratford 7
11 ? Midhurst 1
12 ? Inglewood 2
13 ? Plymouth 1
14 ? Douglas 1
15 ? Eltham 2
16 ? ? 14
Explanation: Rows 1, 2, 3, 5, 6, 7, and 8 have been added because of the grouping [SEX, TOWN]. Rows 4 and 9 have been added because of the grouping [SEX]. Rows 10 through 15 have been added because of the grouping on [TOWN], and row 16 has been included because of the grouping of all rows.
Exercise 10.22: Describe what the difference is between a WITH ROLLUP and a WITH CUBE specification.
Exercise 10.23: Group the MATCHES table on the columns TEAMNO, PLAYERNO, and WON, and add a WITH CUBE specification.