Home > Articles

  • Print
  • + Share This
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. 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.

  • + Share This
  • 🔖 Save To Your Account