- 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.3 Grouping on Two or More Columns
A GROUP BY clause can contain two or more columns—or, in other words, a grouping can consist of two or more columns. We illustrate this with two examples.
Example 10.6. For the MATCHES table, get all the different combinations of team numbers and player numbers.
SELECT TEAMNO, PLAYERNO FROM MATCHES GROUP BY TEAMNO, PLAYERNO
The result is grouped not on one column, but on two. All rows with the same team number and the same player number form a group.
The intermediate result from the GROUP BY clause is:
TEAMNO PLAYERNO MATCHNO WON LOST
------ -------- --------- --------- ---------
1 2 {6} {1} {3}
1 6 {1, 2, 3} {3, 2, 3} {1, 3, 0}
1 8 {8} {0} {3}
1 44 {4} {3} {2}
1 57 {7} {3} {0}
1 83 {5} {0} {3}
2 8 {13} {0} {3}
2 27 {9} {3} {2}
2 104 {10} {3} {2}
2 112 {11, 12} {2, 1} {3, 3}
The end result is:
TEAMNO PLAYERNO
------ --------
1 2
1 6
1 8
1 44
1 57
1 83
2 8
2 27
2 104
2 112
The sequence of the columns in the GROUP BY clause has no effect on the end result of a statement. The following statement, therefore, is equivalent to the previous one:
SELECT TEAMNO, PLAYERNO FROM MATCHES GROUP BY PLAYERNO, TEAMNO
As an example, let us add some aggregation functions to the previous SELECT statement:
SELECT TEAMNO, PLAYERNO, SUM(WON),
COUNT(*), MIN(LOST)
FROM MATCHES
GROUP BY TEAMNO, PLAYERNO
The result is:
TEAMNO PLAYERNO SUM(WON) COUNT(*) MIN(LOST)
------ -------- -------- -------- ---------
1 2 1 1 3
1 6 8 3 0
1 8 0 1 3
1 44 3 1 2
1 57 3 1 0
1 83 0 1 3
2 8 0 1 3
2 27 3 1 2
2 104 3 1 2
2 112 3 2 3
In this example, the grouping is equal to [TEAMNO, PLAYERNO] and the aggregation level of the result is the combination of team number with player number. This aggregation level is lower than that of a statement in which the grouping is equal to [TEAMNO] or [TOWN].
Example 10.7. For each player who has ever incurred at least one penalty, get the player number, the name, and the total amount in penalties incurred.
SELECT P.PLAYERNO, NAME, SUM(AMOUNT)
FROM PLAYERS AS P INNER JOIN PENALTIES AS PEN
ON P.PLAYERNO = PEN.PLAYERNO
GROUP BY P.PLAYERNO, NAME
The result is:
P.PLAYERNO NAME SUM(AMOUNT)
---------- --------- -----------
6 Parmenter 100.00
8 Newcastle 25.00
27 Collins 175.00
44 Baker 130.00
104 Moorman 50.00
Explanation: This example also has a grouping consisting of two columns. The statement would have given the same result if the PEN.PLAYERNO column had been included in the grouping. Work this out for yourself.
Exercise 10.5: For each combination of won–lost sets, get the number of matches won.
Exercise 10.6: For each combination of year–month, get the number of committee members who started in that year and that month.
Exercise 10.7: Group the matches on town of player and division of team, and get the sum of the sets won for each combination of town[nd]division.
Exercise 10.8: For each player who lives in Inglewood, get the name, initials, and number of penalties incurred by him or her.
Exercise 10.9: For each team, get the team number, the division, and the total number of sets won.