Connor: Created page with "

BBC Country Profile

Aggregates

The functions SUM, COUNT, MAX and AVG are "aggregates", each ..."

2012-07-11T09:19:20Z

Created page with "<h3>BBC Country Profile</h3> <h2>Aggregates</h2> <p>The functions <code>SUM</code>, <code>COUNT</code>, <code>MAX</code> and <code>AVG</code> are "aggregates", each ..."

New page

<h3>BBC Country Profile</h3>

<h2>Aggregates</h2>

<p>The functions <code>SUM</code>, <code>COUNT</code>, <code>MAX</code>
and <code>AVG</code> are "aggregates", each
may be applied to a numeric attribute resulting in a single row
being returned by the query. (These functions are even more
useful when used with the <code>GROUP BY</code> clause.)</p>

<h2>Distinct</h2>

<p>By default the result of a <code>SELECT</code> may contain duplicate rows.
We can remove these duplicates using the <code>DISTINCT</code> key word.</p>

<h2>Order by</h2>

<p>ORDER BY permits us to see the result of a SELECT in any
particular order. We may indicate ASC or DESC for ascending (smallest
first, largest last) or descending order.</p>

<div class='qu'>
The total <code>population</code> and <code>GDP</code>
of Europe.
<source lang='sql' class='def'>
SELECT SUM(population), SUM(gdp)
FROM bbc
WHERE region = 'Europe'
</source>

<source lang='sql' class='ans'>
SELECT SUM(population), SUM(gdp)
FROM bbc
WHERE region = 'Europe'
</source>
</div>

<div class='qu'>
What are the regions?
<source lang='sql' class='def'>
SELECT DISTINCT region FROM bbc
</source>

<source lang='sql' class='ans'>
SELECT DISTINCT region FROM bbc
</source>
</div>

<div class='qu'>
Show the <code>name</code> and <code>population</code>
for each country with
a population of more than 100000000. Show countries in descending order of
population.
<source lang='sql' class='def'>
SELECT name, population
FROM bbc
WHERE population > 100000000
ORDER BY population DESC
</source>

<source lang='sql' class='ans'>
SELECT name, population
FROM bbc
WHERE population > 100000000
ORDER BY population DESC
</source>
</div>

Using SUM, Count, MAX, DISTINCT and ORDER BY - Revision history

Connor: Created page with "

BBC Country Profile

Aggregates