# 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 WON, LOST``` 10.6 ```SELECT P.TOWN, 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.TOWN, T.DIVISION ORDER BY P.TOWN``` 10.7 ```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.8 ```SELECT T.TEAMNO, DIVISION, SUM(WON) FROM TEAMS AS T, MATCHES AS M WHERE T.TEAMNO = M.TEAMNO GROUP BY T.TEAMNO, DIVISION```
 10.9 ```SELECT LENGTH(RTRIM(NAME)), COUNT(*) FROM PLAYERS GROUP BY LENGTH(RTRIM(NAME))``` 10.1 ```SELECT ABS(WON - LOST), COUNT(*) FROM MATCHES GROUP BY ABS(WON - LOST)``` 10.11 ```SELECT YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*) FROM COMMITTEE_MEMBERS GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE) ORDER BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)```
 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```