Difference between revisions of "SELECT .. GROUP BY"

From SQLZOO
Jump to: navigation, search
(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...")
 
 
(16 intermediate revisions by 9 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'>Australia</td></tr>
+
<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'>
In a GROUP BY statement only distinct values are shown for the column in the GROUP BY.
+
<div class='params'>schema:scott</div>
 +
<source lang=sql class='tidy'> DROP TABLE games</source>
 +
<source lang=sql class='setup'> CREATE TABLE emp(
 +
  id INTEGER,
 +
  name VARCHAR(20),
 +
  date date);
 +
INSERT INTO emp VALUES (1,'a','jan,10,2014');
 +
INSERT INTO emp VALUES (2,'b','jan,11,2014');
 +
INSERT INTO emp VALUES (3,'c','jan,10,2014');
 +
INSERT INTO emp VALUES (4,'d','jan,11,2014');
 +
</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.  
 
This example shows the continents hosting the Olympics with the count of the number of games held.  
<div class=params>schema:scott</div>
 
<div class=tidy> DROP TABLE games</div>
 
<div class=setup> CREATE TABLE games(
 
  yr INTEGER PRIMARY KEY,
 
  city VARCHAR(10),
 
  continent VARCHAR(10);
 
INSERT INTO games VALUES (
 
 
<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 13:07, 23 April 2014

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
schema:scott
 DROP TABLE games
 CREATE TABLE emp(
  id INTEGER,
  name VARCHAR(20),
  DATE DATE);
INSERT INTO emp VALUES (1,'a','jan,10,2014');
INSERT INTO emp VALUES (2,'b','jan,11,2014');
INSERT INTO emp VALUES (3,'c','jan,10,2014');
INSERT INTO emp VALUES (4,'d','jan,11,2014');

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 games
 GROUP BY continent

See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense