Difference between revisions of "SELECT .. GROUP BY"
From SQLZOO
(Created page with " <h1>SELECT .. GROUP BY</h1> Host cities and continents for the Olympics Games are stored in the table <code>games</code>. Notice that Europe appears in the table twice: <tab...") |
|||
| (4 intermediate revisions by 4 users not shown) | |||
| Line 4: | Line 4: | ||
Notice that Europe appears in the table twice: | Notice that Europe appears in the table twice: | ||
<table> | <table> | ||
| − | <caption align='center'>games</caption> | + | <caption align='center'>'''games'''</caption> |
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th><th align='center'>'''continent'''</th></tr> | <tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th><th align='center'>'''continent'''</th></tr> | ||
| − | <tr><td>2000</td><td align='left'>Sydney</td><td align='left'> | + | <tr><td>2000</td><td align='left'>Sydney</td><td align='left'>Australasia</td></tr> |
<tr><td>2004</td><td align='left'>Athens</td><td align='left'>Europe</td></tr> | <tr><td>2004</td><td align='left'>Athens</td><td align='left'>Europe</td></tr> | ||
<tr><td>2008</td><td align='left'>Beijing</td><td align='left'>Asia</td></tr> | <tr><td>2008</td><td align='left'>Beijing</td><td align='left'>Asia</td></tr> | ||
<tr><td>2012</td><td align='left'>London</td><td align='left'>Europe</td></tr> | <tr><td>2012</td><td align='left'>London</td><td align='left'>Europe</td></tr> | ||
</table> | </table> | ||
| + | |||
<div class='ht'> | <div class='ht'> | ||
| − | |||
| − | |||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| − | < | + | <source lang=sql class='tidy'> DROP TABLE games</source> |
| − | < | + | <source lang=sql class='setup'> CREATE TABLE games( |
| − | + | yr INTEGER, | |
| − | + | city VARCHAR(20), | |
| − | + | continent VARCHAR(20)); | |
| − | INSERT INTO games VALUES ( | + | INSERT INTO games VALUES (2000,'Sydney','Australasia'); |
| + | INSERT INTO games VALUES (2004,'Athens','Europe'); | ||
| + | INSERT INTO games VALUES (2008,'Beijing','Asia'); | ||
| + | INSERT INTO games VALUES (2012,'London','Europe'); | ||
| + | </source> | ||
| + | In a GROUP BY statement only <i>distinct</i> values are shown for the column in the GROUP BY. | ||
| + | This example shows the continents hosting the Olympics with the count of the number of games held. | ||
| + | <source lang='sql' class='def e-oracle'> | ||
| + | SELECT continent, COUNT(yr) FROM scott.games | ||
| + | GROUP BY continent | ||
| + | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
SELECT continent, COUNT(yr) FROM games | SELECT continent, COUNT(yr) FROM games | ||
Revision as of 18:00, 22 January 2013
SELECT .. GROUP BY
Host cities and continents for the Olympics Games are stored in the table games.
Notice that Europe appears in the table twice:
| yr | city | continent |
|---|---|---|
| 2000 | Sydney | Australasia |
| 2004 | Athens | Europe |
| 2008 | Beijing | Asia |
| 2012 | London | Europe |
schema:scott
DROP TABLE games
CREATE TABLE games( yr INTEGER, city VARCHAR(20), continent VARCHAR(20)); INSERT INTO games VALUES (2000,'Sydney','Australasia'); INSERT INTO games VALUES (2004,'Athens','Europe'); INSERT INTO games VALUES (2008,'Beijing','Asia'); INSERT INTO games VALUES (2012,'London','Europe');
In a GROUP BY statement only distinct values are shown for the column in the GROUP BY. This example shows the continents hosting the Olympics with the count of the number of games held.
SELECT continent, COUNT(yr) FROM scott.games GROUP BY continent
SELECT continent, COUNT(yr) FROM games GROUP BY continent
See also
