Difference between revisions of "SELECT .. GROUP BY"

From SQLZOO
Jump to: navigation, search
 
(12 intermediate revisions by 4 users not shown)
Line 9: Line 9:
 
<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>2019</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>
 +
<tr><td>2016</td><td align='left'>Rio</td><td align='left'>South America</td></tr>
 
</table>
 
</table>
 
   
 
   
Line 23: Line 24:
 
INSERT INTO games VALUES (2008,'Beijing','Asia');
 
INSERT INTO games VALUES (2008,'Beijing','Asia');
 
INSERT INTO games VALUES (2012,'London','Europe');
 
INSERT INTO games VALUES (2012,'London','Europe');
 +
INSERT INTO games VALUES (2016,'Rio','South America');
 
</source>
 
</source>
 
In a GROUP BY statement only <i>distinct</i> values are shown for the column in the GROUP BY.
 
In a GROUP BY statement only <i>distinct</i> values are shown for the column in the GROUP BY.
Line 29: Line 31:
 
SELECT continent, COUNT(yr) FROM scott.games
 
SELECT continent, COUNT(yr) FROM scott.games
 
  GROUP BY continent
 
  GROUP BY continent
</source>
+
</source>  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT continent, COUNT(yr) FROM games
 
SELECT continent, COUNT(yr) FROM games

Latest revision as of 09:50, 11 July 2016

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:

games
yrcitycontinent
2000SydneyAustralasia
2004AthensEurope
2008BeijingAsia
2012LondonEurope
2016RioSouth America
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');
INSERT INTO games VALUES (2016,'Rio','South America');

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