Difference between revisions of "SELECT .. GROUP BY"

From SQLZOO
Jump to: navigation, search
(Blanked the page)
Line 1: Line 1:
  
<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:
 
<table>
 
<caption align='center'>'''games'''</caption>
 
<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'>Australasia</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>2012</td><td align='left'>London</td><td align='left'>Europe</td></tr>
 
</table>
 
 
<div class='ht'>
 
<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.
 
<source lang='sql' class='def'>
 
SELECT continent, COUNT(yr) FROM games
 
GROUP BY continent
 
</source>
 
</div>
 
 
<p>See also</p>
 
<ul>
 
  <li>[[SUM_and_COUNT |SUM and COUNT]]</li>
 
  <li>[[SELECT_.._WHERE |SELECT WHERE]]</li>
 
</ul>
 

Revision as of 12:07, 23 April 2014

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense