This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.

This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.

For each '''continent''' show the '''continent''' and number of countries.

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

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

</source>

</source>

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

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

−

SELECT region, COUNT(name)

+

SELECT continent, COUNT(name)

−

FROM bbc

+

FROM world

−

GROUP BY(region)

+

GROUP BY(continent)

</source>

</source>

</div>

</div>

<div class='qu'>

<div class='qu'>

−

For each '''region''' show the '''region''' and number of countries with populations of at least 10 million.

+

For each '''continent''' show the '''continent''' and number of countries with populations of at least 10 million.

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

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

</source>

</source>

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

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

−

SELECT region, COUNT(name)

+

SELECT continent, COUNT(name)

−

FROM bbc

+

FROM world

WHERE population >= 10000000

WHERE population >= 10000000

−

GROUP BY(region)

+

GROUP BY(continent)

</source>

</source>

</div>

</div>

<div class='qu'>

<div class='qu'>

−

List the regions with total populations of at least 100 million.

+

List the continents with total populations of at least 100 million.

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

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

</source>

</source>

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

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

−

SELECT region

+

SELECT continent

−

FROM bbc

+

FROM world

−

GROUP BY region

+

GROUP BY continent

HAVING SUM(population)>= 100000000

HAVING SUM(population)>= 100000000

</source>

</source>

Revision as of 23:04, 8 December 2012

World Country Profile: Aggregate functions

This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.