Difference between revisions of "Aggregates with DISTINCT"

From SQLZOO
Jump to: navigation, search
(Created page with " <h3>Nobel database</h3> <h2>COUNT DISTINCT</h2> <p>You can find the number of <i>different</i> values using COUNT with DISTINCT</p> <p>In 1915 four prizes were award...")
 
Line 5: Line 5:
 
   <p>You can find the number of <i>different</i> values using COUNT with DISTINCT</p>
 
   <p>You can find the number of <i>different</i> values using COUNT with DISTINCT</p>
 
   <p>In 1915 four prizes were awarded in three different subjects:</p>
 
   <p>In 1915 four prizes were awarded in three different subjects:</p>
<table><tr><td><pre>SELECT yr, subject, winner
+
<table><td><pre>SELECT yr, subject, winner
 
   FROM nobel
 
   FROM nobel
 
  WHERE yr=1915</pre>
 
  WHERE yr=1915</pre>
Line 34: Line 34:
 
<td>William Bragg</td>
 
<td>William Bragg</td>
 
</tr>
 
</tr>
</table></td></tr>
+
</table></td>
 
</table>
 
</table>
  
Line 48: Line 48:
 
SELECT COUNT(subject), COUNT(DISTINCT subject)
 
SELECT COUNT(subject), COUNT(DISTINCT subject)
 
   FROM nobel
 
   FROM nobel
  WHERE yr = 1915'
+
  WHERE yr = 1915
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 10:59, 11 July 2012

Nobel database

COUNT DISTINCT

You can find the number of different values using COUNT with DISTINCT

In 1915 four prizes were awarded in three different subjects:

SELECT yr, subject, winner
  FROM nobel
 WHERE yr=1915
yr subject winner
1915 Chemistry Richard Willstätter
1915 Literature Romain Rolland
1915 Physics Lawrence Bragg
1915 Physics William Bragg

COUNT(subject) gives 4; COUNT(DISTINCT subject) gives 3.

SELECT COUNT(subject), COUNT(DISTINCT subject)
  FROM nobel
 WHERE yr = 1915
SELECT COUNT(subject), COUNT(DISTINCT subject)
  FROM nobel
 WHERE yr = 1915
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense