Using SUM, Count, MAX, DISTINCT and ORDER BY

From SQLZoo
Language:Project:Language policy English  • 日本語

BBC Country Profile

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 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'
SELECT SUM(population), SUM(gdp)
  FROM bbc
  WHERE region = 'Europe'

What are the regions? 

SELECT DISTINCT region FROM bbc
SELECT DISTINCT region FROM bbc

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
SELECT name, population
  FROM bbc
  WHERE population > 100000000
  ORDER BY population DESC
Retrieved from ‘http://sqlzoo.net/w/index.php?title=Using_SUM,_Count,_MAX,_DISTINCT_and_ORDER_BY&oldid=39298’