### 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. The next two examples illustrate this topic.

**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 and 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, name, and total amount of 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 also been added to the grouping. Work this out by yourself.

**Exercise 10.5: **For each combination of won-lost sets in the MATCHES table, get the number of matches won.

**Exercise 10.6: **Group the matches on town of player and division of team, and get the sum of the number of sets won for each combination of town-division.

**Exercise 10.7: **For each player who lives in Inglewood, get the name, initials, and number of penalties incurred by him or her.

**Exercise 10.8: **For each team, get the team number, division, and total number of sets won.

### 10.4 Grouping on Expressions

Until now, we have shown only examples in which the result was grouped on one or more columns, but what happens when we group on expressions? See the next two examples.

**Example 10.8:** For each year in the PENALTIES table, get the number of penalties paid.

SELECT YEAR(PAYMENT_DATE), COUNT(*)
FROM PENALTIES
GROUP BY YEAR(PAYMENT_DATE)

The intermediate result from the GROUP BY clause is:

YEAR(PAYMENT_DATE) PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT
------------------ --------- ---------- ------------ --------
1980 {1, 5, 6} {6, 44, 8} {1980-12-08, {100.00,
1980-12-08, 25,00,
1980-12-08} 25,00}
1981 {2} {44} {1981-05-05} {75,00}
1982 {7} {44} {1982-12-30} {30,00}
1983 {3} {27} {1983-09-10} {100,00}
1984 {4, 8} {104, 27} {1984-12-08, {50,00,
1984-11-12} 75,00}

The result is:

YEAR(PAYMENT_DATE) COUNT(*)
------------------ --------
1980 3
1981 1
1982 1
1983 1
1984 2

**Explanation:** The result is now grouped on the values of the scalar expression YEAR(PAYMENT_DATE). Rows for which the value of the expression YEAR(PAYMENT_ DATE) is equal form a group.

**Example 10.9:** Group the players on the basis of their player numbers. Group 1 should contain the players with number 1 up to and including 24. Group 2 should contain the players with numbers 25 up to and including 49, and so on. For each group, get the number of players and the highest player number.

SELECT TRUNCATE(PLAYERNO/25,0), COUNT(*), MAX(PLAYERNO)
FROM PLAYERS
GROUP BY TRUNCATE(PLAYERNO/25,0)

The result is:

TRUNCATE(PLAYERNO/25,0) COUNT(*) MAX(PLAYERNO)
----------------------- -------- -------------
0 4 8
1 4 44
2 1 57
3 2 95
4 3 112

The scalar expression on which rows are grouped can be rather complex. This can consist of system variables, user variables, functions, and calculations. Even certain scalar subqueries are allowed.

**Exercise 10.9: **Group the players on the length of their names and get the number of players for each length.

**Exercise 10.10: **For each match, determine the difference between the number of sets won and lost, and group the matches on that difference.

**Exercise 10.11: **For each combination of year-month in the COMMITTEE_ MEMBERS table, get the number of committee members who started in that year and that month.

### 10.5 Grouping of Null Values

If grouping is required on a column that contains null values, all these null values form one group because a GROUP BY clause applies a vertical comparison. This is in accordance with the rules described in Section 9.5.

**Example 10.10:** Find the different league numbers.

SELECT LEAGUENO
FROM PLAYERS
GROUP BY LEAGUENO

The result is:

LEAGUENO
--------
1124
1319
1608
2411
2513
2983
6409
6524
7060
8467
?

**Explanation: **Players 7, 28, 39, and 95 do not have a league number and, therefore, form one group (the last row) in the end result.