- 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

## 10.7 Complex Examples with GROUP BY

Here are several other examples to illustrate the extensive possibilities of the `GROUP BY` clause.

#### Example 10.12. What is the average total amount of penalties for players who live in Stratford and Inglewood?

SELECT AVG(TOTAL) FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL FROM PENALTIES GROUP BY PLAYERNO) AS TOTALS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford' OR TOWN = 'Inglewood')

The result is:

AVG(TOTAL) ---------- 85

**Explanation:** The intermediate result of the subquery in the `FROM` clause is a table consisting of two columns, called `PLAYERNO` and `TOTAL`, and contains five rows (players 6, 8, 27, 44, and 104). This table is passed on to the `WHERE` clause, where a subquery selects players from Stratford and Inglewood (players 6, 8, and 44). Finally, the average is calculated in the `SELECT` clause of the column `TOTAL`.

#### Example 10.13. For each player who incurred penalties and is captain, get the player number, the name, the number of penalties that he or she incurred, and the number of teams that he or she captains.

SELECT PLAYERS.PLAYERNO, NAME, NUMBER_OF_PENALTIES, NUMBER_OF_TEAMS FROM PLAYERS, (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_PENALTIES FROM PENALTIES GROUP BY PLAYERNO) AS NUMBER_PENALTIES, (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_TEAMS FROM TEAMS GROUP BY PLAYERNO) AS NUMBER_TEAMS WHERE PLAYERS.PLAYERNO = NUMBER_PENALTIES.PLAYERNO AND PLAYERS.PLAYERNO = NUMBER_TEAMS.PLAYERNO

The result is:

PLAYERNO NAME NUMBER_OF_PENALTIES NUMBER_OF_TEAMS -------- --------- ------------------- --------------- 6 Parmenter 1 1 27 Collins 2 1

**Explanation:** The `FROM` clause contains two subqueries that both have a `GROUP BY` clause.

The previous statement could have been formulated more easily by including subqueries in the `SELECT` clause, which makes `GROUP BY` clauses no longer required; see the next example. Now, the only difference is that all players appear in the result.

SELECT PLAYERS.PLAYERNO, NAME, (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES, (SELECT COUNT(*) FROM TEAMS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS FROM PLAYERS

#### Example 10.14. Get the player number and the total number of penalties for each player who played a match.

SELECT DISTINCT M.PLAYERNO, NUMBERP FROM MATCHES AS M LEFT OUTER JOIN (SELECT PLAYERNO, COUNT(*) AS NUMBERP FROM PENALTIES GROUP BY PLAYERNO) AS NP ON M.PLAYERNO = NP.PLAYERNO

**Explanation:** In this statement, the subquery creates the following intermediate result (this is the `NP` table):

PLAYERNO NUMBERP -------- ------- 6 1 8 1 27 2 44 3 104 1

Next, this table is joined with the `MATCHES` table. We execute a left outer join, so no players disappear from this table. The final result is:

PLAYERNO NUMBERP -------- ------- 2 ? 6 1 8 1 27 2 44 3 57 ? 83 ? 104 1 112 ?

#### Example 10.15. Group the penalties on the basis of payment date. Group 1 should contain all penalties between January 1, 1980, and June 30, 1982; group 2 should contain all penalties between July 1, 1981, and December 31, 1982; and group 3 should contain all penalties between January 1, 1983, and December 31, 1984. Get for each group the sum of all penalties.

SELECT GROUPS.PGROUP, SUM(P.AMOUNT) FROM PENALTIES AS P, (SELECT 1 AS PGROUP, '1980-01-01' AS START, '1981-06-30' AS END UNION SELECT 2, '1981-07-01', '1982-12-31' UNION SELECT 3, '1983-01-01', '1984-12-31') AS GROUPS WHERE P.PAYMENT_DATE BETWEEN START AND END GROUP BY GROUPS.PGROUP ORDER BY 1

The result is:

GROUP SUM(P.AMOUNT) ----- ------------- 1 225.00 2 30.00 3 225.00

**Explanation:** In the `FROM` clause, a new (virtual) table is created in which the three groups have been defined. This `GROUPS` table is joined with the `PENALTIES` table. A `BETWEEN` operator is used to join the two tables. If there are penalties that fall outside these groups with respect to payment date, they will not be included in the result.

#### Example 10.16. For each penalty, get the penalty amount plus the sum of that amount and the amounts of all penalties with a lower payment number (cumulative value).

SELECT P1.PAYMENTNO, P1.AMOUNT, SUM(P2.AMOUNT) FROM PENALTIES AS P1, PENALTIES AS P2 WHERE P1.PAYMENTNO >= P2. PAYMENTNO GROUP BY P1. PAYMENTNO, P1.AMOUNT ORDER BY P1. PAYMENTNO

For convenience, we assume that the `PENALTIES` table consists of the following three rows only (you can create this, too, by temporarily removing all penalties with a number greater than 3):

PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT --------- -------- ------------ ------ 1 6 1980-12-08 100 2 44 1981-05-05 75 3 27 1983-09-10 100

The desired result is:

PAYMENTNO AMOUNT SUM --------- ------ --- 1 100 100 2 75 175 3 100 275

The intermediate result of the `FROM` clause (we show only the columns `PAYMENTNO` and `AMOUNT`):

P1.PAYNO P1.AMOUNT P2.PAYNO P2.AMOUNT -------- --------- -------- --------- 1 100 1 100 1 100 2 75 1 100 3 100 2 75 1 100 2 75 2 75 2 75 3 100 3 100 1 100 3 100 2 75 3 100 3 100

The intermediate result of the `WHERE` clause:

P1.PAYNO P1.AMOUNT P2.PAYNO P2.AMOUNT -------- --------- -------- --------- 1 100 1 100 2 75 1 100 2 75 2 75 3 100 1 100 3 100 2 75 3 100 3 100

The intermediate result of the `GROUP BY` clause:

P1.PAYNO P1.AMOUNT P2.PAYNO P2.AMOUNT -------- --------- -------- -------------- 1 100 {1} {100} 2 75 {1, 2} {100, 75} 3 100 {1, 2, 3} {100, 75, 100}

The intermediate result of the `SELECT` clause:

P1.PAYNO P1.AMOUNT SUM(P2.AMOUNT) -------- --------- -------------- 1 100 100 2 75 175 3 100 275

This final result is equal to the desired table.

Most joins in this book and in reality are equi joins. Non-equi joins are rare. The previous statement is an example that shows that non-equi joins can be useful and that powerful statements can be formulated with them.

#### Example 10.17. For each penalty, get the payment number, the penalty amount, and the percentage that the amount forms of the sum of all amounts (again, we use the same `PENALTIES` table as in the previous example).

SELECT P1.PAYMENTNO, P1.AMOUNT, (P1.AMOUNT * 100) / SUM(P2.AMOUNT) FROM PENALTIES AS P1, PENALTIES AS P2 GROUP BY P1.PAYMENTNO, P1.AMOUNT ORDER BY P1.PAYMENTNO

The intermediate result of the `FROM` clause is equal to that of the previous example. However, the intermediate result of the `GROUP BY` clause differs:

P1.PAYNO P1.AMOUNT P2.PAYNO P2.AMOUNT -------- --------- -------- -------------- 1 100 {1, 2, 3} {100, 75, 100} 2 75 {1, 2, 3} {100, 75, 100} 3 100 {1, 2, 3} {100, 75, 100}

The intermediate result of the `SELECT` clause:

P1.PAYNO P1.AMOUNT (P1.AMOUNT * 100) / SUM(P2.AMOUNT) -------- --------- ---------------------------------- 1 100 36.36 2 75 27.27 3 100 36.36

Find out whether this is the final result as well.

Exercise 10.14: How many players live in a town, on average?

Exercise 10.15: For each team, get the team number, the division, and the number of players that played matches for that team.

Exercise 10.16: For each player, get the player number, the name, the sum of all penalties that he or she incurred, and the number of teams from the first division that he or she captains.

Exercise 10.17: For each team captained by a player who lives in Stratford, get the team number and the number of players who have won at least one match for that team.

Exercise 10.18: For each player, get the player number, the name, and the difference between the year in which he or she joined the club and the average year of joining the club.

Exercise 10.19: For each player, get the player number, the name, and the difference between the year in which he or she joined the club and the average year in which players who live in the same town joined the club.