Using GROUP BY and HAVING. - Revision history

Andr3w at 23:18, 8 December 2012

2012-12-08T23:18:40Z

Andr3w at 23:11, 8 December 2012

2012-12-08T23:11:38Z

Andr3w at 23:10, 8 December 2012

2012-12-08T23:10:44Z

Connor at 08:21, 18 July 2012

2012-07-18T08:21:38Z

Connor: Created page with "

BBC Country Profile

GROUP BY and HAVING

By including a GROUP BY clause functions such as SUM and CO..."

2012-07-11T09:29:28Z


Created page with "<h3>BBC Country Profile</h3>  <p><code>GROUP BY</code> and <code>HAVING</code> <p>By including a <code>GROUP BY</code> clause functions such as   <code>SUM</code> and <code>CO..."
New page
<h3>BBC Country Profile</h3>



<p><code>GROUP BY</code> and <code>HAVING</code>

<p>By including a <code>GROUP BY</code> clause functions such as

  <code>SUM</code> and <code>COUNT</code>

  are applied to groups of items sharing values. When you specify 

  <code>GROUP BY region </code> 

   the result is that you get only one row for each different value

  of <code>region</code> 

  . All the other columns must be "aggregated" by one of <code>SUM</code>,

  <code>COUNT</code> ...</p>

<p>The <code>HAVING</code> clause allows use to filter the groups which are

  displayed. The <code>WHERE</code> clause filters rows before the aggregation,

  the <code>HAVING</code> clause filters after the aggregation.</p>

  <p>If a <code>ORDER BY</code> clause is included we can refer to columns by

  their position.</p>



<div class='qu'>

For each region show the number of countries:

   bbc('''name''', region, area, population, gdp)

<source lang='sql' class='def'>

SELECT region, COUNT(name)

  FROM bbc

 GROUP BY region

</source>



<source lang='sql' class='ans'>

SELECT region, COUNT(name)

  FROM bbc

 GROUP BY region

</source>

</div>



<div class='qu'>

For each region show the total population:

   bbc('''name''', region, area, population, gdp)

<source lang='sql' class='def'>

SELECT region, SUM(population)

  FROM bbc

 GROUP BY region

</source>



<source lang='sql' class='ans'>

SELECT region, SUM(population)

  FROM bbc

 GROUP BY region

</source>

</div>



<div class='qu'>

WHERE and GROUP BY

The WHERE filter takes place before the aggregating function.

For each relevant region show the number of countries that has a population

of at least 200000000.

<source lang='sql' class='def'>

SELECT region, COUNT(name)

  FROM bbc

 WHERE population>200000000

 GROUP BY region

</source>



<source lang='sql' class='ans'>

SELECT region, COUNT(name)

  FROM bbc

 WHERE population>200000000

 GROUP BY region

</source>

</div>



<div class='qu'>

GROUP BY and HAVING

The HAVING clause is tested after the GROUP BY. You can test the aggregated

values with a HAVING clause.

Show the total population of those regions with a total population of at

least half a billion.

<source lang='sql' class='def'>

SELECT region, SUM(population)

  FROM bbc

 GROUP BY region

HAVING SUM(population)>500000000

</source>



<source lang='sql' class='ans'>

SELECT region, SUM(population)

  FROM bbc

 GROUP BY region

HAVING SUM(population)>500000000

</source>

@@ Line 18: / Line 18: @@
 <div class='qu'>
 For each continent show the number of countries:
-   world('''name''', continent, area, population, gdp)
+ world('''name''', continent, area, population, gdp)
 <source lang='sql' class='def'>
 SELECT continent, COUNT(name)
@@ Line 34: / Line 34: @@
 <div class='qu'>
 For each continent show the total population:
-   world('''name''', continent, area, population, gdp)
+ world('''name''', continent, area, population, gdp)
 <source lang='sql' class='def'>
 SELECT continent, SUM(population)