- 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.12 Combining Grouping Sets
Multiple groupings can be included in one select block. Simple group by expressions may be combined with grouping sets specifications, multiple grouping sets specifications may be specified and even two rollups may be specified. However, the effect of this combining needs some explanation.
If a grouping sets specification is combined with one or more simple group by expressions, the latter simply is added to the grouping sets specification. For example, the specification GROUPING SETS ((E 1)), E 2, E 3 is equal to GROUPING SETS ((E 1, E 2, E 3)). If the grouping sets specification contains two groupings, the simple expressions are added to both groupings. The specification GROUPING SETS ((E 1), (E 2)), E 3 is, for example, equal to GROUPING SETS ((E 1,E 3),(E 2,E 3)).
If two grouping sets specifications are included in one GROUP BY clause, some kind of multiplication of the specifications takes place. For example, the specification GROUPING SETS ((E 1), (E 2)), GROUPING SETS ((E 3)) contains two grouping sets specifications, in which the first consists of two groupings and the second of one grouping. SQL turns it into GROUPING SETS ((E 1, E 3), (E 2, E 3)). Now the expression E 3 has been added to both groupings of the first grouping sets specification. The specification GROUPING SETS ((E 1), (E 2)), GROUPING SETS ((E 3), (E 4)) is turned into GROUPING SETS ((E 1, E 3), (E 1, E 4), (E 2, E 3), (E 2, E 4)). It is obvious that E 1 is linked to both groupings of the other grouping sets specifications. The same applies to E 2.
Finally, the specification GROUPING SETS ((E 1), (E 2)), GROUPING SETS ((E 3), (E 4)), E 5 is turned into GROUPING SETS ((E 1, E 3, E 5)), (E 1, E 4, E 5), (E 2, E 3, E 5), (E 2, E 4, E 5)).
Table 10.4 gives a few abstract examples of certain GROUP BY clauses in which several grouping sets specifications appear, including the groupings that are executed. Again, E 1, E 2, E 3, and E 4 stand for random expressions, and the symbol ∪ represents the union operator.
Table 10.4. Combining Grouping Sets Specifications
|
GROUP BY Clause |
Groupings |
|
GROUP BY GROUPING SETS (E1, E2), E3 |
[E1, E3] ∪ [E2, E3] |
|
GROUP BY E1, GROUPING SETS (E2, E3) |
[E1, E2] ∪ [E1, E3] |
|
GROUP BY GROUPING SETS ((E1, E2)), E3 |
[E1, E2, E3] |
|
GROUP BY GROUPING SETS ((E1, E2), (E3, E4)), E5 |
[E1, E2, E5] ∪ [E3, E4, E5] |
|
GROUP BY ROLLUP (E1, E2)), E3 |
[E1, E2, E3] ∪ [E1, E2] ∪ [E1] ∪ [] |
|
GROUP BY GROUPING SETS (E1, E2), |
[E1, E3] ∪ [E1, E4] ∪ |
|
GROUPING SETS (E3, E4) |
[E2, E3] ∪ [E2, E4] ∪ |
|
GROUP BY GROUPING SETS (E1, ROLLUP (E2, E3)) |
[E1] ∪ [E2, E3] ∪ [E2] ∪ [] |
|
GROUP BY GROUPING SETS ((E1, ROLLUP (E2))) |
[E1, E2] ∪ [E1] ∪ [] |
|
GROUP BY ROLLUP (E1, E2), ROLLUP (E3, E4) |
[E1, E2, E3, E4] ∪ [E1, E3, E4] ∪ [E3, E4] ∪ [E1, E2, E3] ∪ [E1, E3] ∪ [E3] ∪ [E1, E2] ∪ [E1] ∪ [] |