# SELECT Statement: The GROUP BY Clause in SQL

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