Aggregates
The functions SUM, COUNT, MAX and AVG 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 in conjunction with the GROUP BY clause.)
Distinct
By default the result of a SELECT may contain duplicate rows. We can remove these duplicates using the DISTINCT key word.
Order by
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.
The total population and GDP of Europe.
SELECT SUM(population), SUM(gdp)
FROM bbc
WHERE region = 'Europe'
What are the regions?
SELECT DISTINCT region FROM bbc
| REGION |
|---|
| South Asia |
| Asia-Pacific |
| Middle East |
| Europe |
| Africa |
| Americas |
Show the
name and population for each country with a population of more than 100000000. Show countries in descending order of population. SELECT name, population
FROM bbc
WHERE population > 100000000
ORDER BY population DESC