# SELECT Statement: The GROUP BY Clause in SQL

• Print
This chapter is from the book

### This chapter is from the book 

 10.1 ```SELECT JOINED FROM PLAYERS GROUP BY JOINED ``` 10.2 ```SELECT JOINED, COUNT(*) FROM PLAYERS GROUP BY JOINED ``` 10.3 ```SELECT PLAYERNO, AVG(AMOUNT), COUNT(*) FROM PENALTIES GROUP BY PLAYERNO ``` 10.4 ```SELECT TEAMNO, COUNT(*), SUM(WON) FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE DIVISION = 'first') GROUP BY TEAMNO ``` 10.5 ```SELECT WON, LOST, COUNT(*) FROM MATCHES WHERE WON > LOST GROUP BY WON, LOST ORDER BY 1, 2 ``` 10.6 ```SELECT YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*) FROM COMMITTEE_MEMBERS GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE) ORDER BY 1, 2 ``` 10.7 ```SELECT P.NAME, T.DIVISION, SUM(WON) FROM (MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO) INNER JOIN TEAMS AS T ON M.TEAMNO = T.TEAMNO GROUP BY P.NAME, T.DIVISION ORDER BY 1 ``` 10.8 ```SELECT NAME, INITIALS, COUNT(*) FROM PLAYERS AS P INNER JOIN PENALTIES AS PEN ON P.PLAYERNO = PEN.PLAYERNO WHERE P.TOWN = 'Inglewood' GROUP BY P.PLAYERNO, NAME, INITIALS ``` 10.9 ```SELECT T.TEAMNO, DIVISION, SUM(WON) FROM TEAMS AS T, MATCHES AS M WHERE T.TEAMNO = M.TEAMNO GROUP BY T.TEAMNO, DIVISION ``` 10.1 ```SELECT LENGTH(RTRIM(NAME)), COUNT(*) FROM PLAYERS GROUP BY LENGTH(RTRIM(NAME)) ``` 10.11 ```SELECT ABS(WON - LOST), COUNT(*) FROM MATCHES GROUP BY ABS(WON - LOST) ``` 10.12 The result of the DIVISION column has not been grouped, while this column appears in the SELECT clause. The NAME column cannot appear like this in the SELECT clause because the result has not been grouped on the full NAME column. The PLAYERNO column appears in the SELECT clause, while the result has not been grouped; furthermore, the column does not appear as parameter of an aggregation function. 10.13 Superfluous. Not superfluous. Superfluous. 10.14 ```SELECT AVG(NUMBERS) FROM (SELECT COUNT(*) AS NUMBERS FROM PLAYERS GROUP BY TOWN) AS TOWNS ``` 10.15 ```SELECT TEAMS.TEAMNO, DIVISION, NUMBER_PLAYERS FROM TEAMS LEFT OUTER JOIN (SELECT TEAMNO, COUNT(*) AS NUMBER_PLAYERS FROM MATCHES GROUP BY TEAMNO) AS M ON (TEAMS.TEAMNO = M.TEAMNO)``` 10.16 ```SELECT PLAYERS.PLAYERNO, NAME, SUM_AMOUNT, NUMBER_TEAMS FROM (PLAYERS LEFT OUTER JOIN (SELECT PLAYERNO, SUM(AMOUNT) AS SUM_AMOUNT FROM PENALTIES GROUP BY PLAYERNO) AS TOTALS ON (PLAYERS.PLAYERNO = TOTALS.PLAYERNO)) LEFT OUTER JOIN (SELECT PLAYERNO, COUNT(*) AS NUMBER_TEAMS FROM TEAMS WHERE DIVISION = 'first' GROUP BY PLAYERNO) AS NUMBERS ON (PLAYERS.PLAYERNO = NUMBERS.PLAYERNO) ``` 10.17 ```SELECT TEAMNO, COUNT(DISTINCT PLAYERNO) FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM PLAYERS AS P INNER JOIN TEAMS AS T ON P.PLAYERNO = T.PLAYERNO AND TOWN = 'Stratford') AND WON > LOST GROUP BY TEAMNO ``` 10.18 ```SELECT PLAYERNO, NAME, JOINED - AVERAGE FROM PLAYERS, (SELECT AVG(JOINED) AS AVERAGE FROM PLAYERS) AS T ``` 10.19 ```SELECT PLAYERNO, NAME, JOINED - AVERAGE FROM PLAYERS, (SELECT TOWN, AVG(JOINED) AS AVERAGE FROM PLAYERS GROUP BY TOWN) AS TOWNS WHERE PLAYERS.TOWN = TOWNS.TOWN ``` 10.2 ```SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO WITH ROLLUP ``` 10.21 ```SELECT P.NAME, T.DIVISION, SUM(WON) FROM (MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO) INNER JOIN TEAMS AS T ON M.TEAMNO = T.TEAMNO GROUP BY P.NAME, T.DIVISION WITH ROLLUP ``` 10.22 The WITH ROLLUP specification calculates all levels of aggregation; at the bottom is a grouping based upon the expressions specified. The WITH CUBE specification returns much more data. For every possible combination of expressions specified, groupings are performed. 10.23 ```SELECT ROW_NUMBER() OVER () AS SEQNO, TEAMNO, PLAYERNO, WON, COUNT(*) FROM MATCHES GROUP BY TEAMNO, PLAYERNO, WON WITH CUBE ORDER BY 2, 3 ``` 10.24 ```SELECT COUNT(*) FROM MATCHES GROUP BY GROUPING SETS (()) ``` 10.25 ```SELECT TEAMNO, PLAYERNO, COUNT(*) FROM MATCHES GROUP BY GROUPING SETS ((TEAMNO, PLAYERNO), (TEAMNO), ()) ORDER BY 1, 2 ``` 10.26 [] ∪ [E1] ∪ [E2] [E1] ∪ [E2, E3] ∪ [E3, E4, E5] [E1, E2] ∪ [] ∪ [E3] 10.27 ```SELECT TEAMNO, PLAYERNO, COUNT(*) FROM MATCHES WHERE WON > LOST GROUP BY ROLLUP (TEAMNO, PLAYERNO) ORDER BY 1, 2``` 10.28 ```SELECT P.TOWN, P.SEX, M.TEAMNO, COUNT(*) FROM MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO GROUP BY CUBE (P.TOWN, P.SEX, M.TEAMNO) ORDER BY 1, 2, 3 ```