SELECT Statement: The GROUP BY Clause in SQL

• Print
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.