GISQ: Tutorial One: Notes 5






A Gentle
Introduction
to



SQL

Tutorial One
Notes 5

CIA World Factbook

GROUP BY and HAVING

By including a GROUP BY clause functions such as SUM and COUNT are
applied to groups of items sharing values.


The HAVING clause allows use to filter the groups which are displayed.
If a ORDER BY clause is included we can refer to columns by their
position.


The regions with total populations and areas, sorted by population.

SELECT region, SUM(population), SUM(area) FROM cia
GROUP BY region ORDER BY 2 DESC


regionSUM OF populationSUM OF area
Asia280942687632405756
Africa71536998326558788
Southeast Asia5224586804532682
Europe3937088233776586
North America38630281121321592
South America31915286617818505
Middle East2273952966062418
Ethnic Groups in Eastern Europe, Europe1230570961166168
Commonwealth of Independent States - European States78142073961400
Central America and the Caribbean67810131758888
Commonwealth of Independent States - Central Asian States554665433994400
Oceania286871548509527
World22291188787
Arctic Region3265232341036
Antarctic Region012331


The regions with total populations and areas, sorted by population.
Include only those regions with names that include "America".
SELECT region, SUM(population), SUM(area) FROM cia
GROUP BY region HAVING region LIKE '%America%'

regionSUM OF populationSUM OF area
North America38630281121321592
South America31915286617818505
Central America and the Caribbean67810131758888