CIA World Factbook

Other features of SELECT: SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY

The functions SUM, COUNT, MAX and AVG 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.

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

ORDER BY permits us to see the result of a SELECT in any particular order.

• The total population and GDP in millions of Europe.

  SELECT SUM(population)/1000000, SUM(gdp)/1000000
    FROM cia
    WHERE region = 'Europe'

  
  

  SUM OF population / 1000000	SUM OF gdp / 1000000
  393.708823	6584875

• How many different regions are there?

  SELECT COUNT(DISTINCT region) FROM cia

  
  

  COUNT OF region
  15

• Show the large countries by order of population.

  SELECT name, population
    FROM cia
    WHERE population > 100000000
    ORDER BY population DESC

  name	population
  China	1203097268
  India	936545814
  United States	263814032
  Indonesia	203583886
  Brazil	160737489
  Russia	149909089
  Pakistan	131541920
  Bangladesh	128094948
  Japan	125506492
  Nigeria	101232251