␡
- 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
< Back
Page 13 of 13
This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.13 Answers
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.10 |
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 |
|
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.20 |
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 |
|
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
|
< Back
Page 13 of 13