Skip to content

Latest commit

 

History

History
142 lines (106 loc) · 2.95 KB

5.2 SUM and COUNT.md

File metadata and controls

142 lines (106 loc) · 2.95 KB
Raw

SUM and COUNT

Link to page - https://sqlzoo.net/wiki/SUM_and_COUNT

This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.

1. Total world population

Show the total population of the world.

SELECT SUM(population) as "total people"
FROM world
total people
7118632738

2. List of continents

List all the continents - just once each.

select DISTINCT continent FROM world
continent
Africa
Asia
Caribbean
Eurasia
Europe
North America
Oceania
South America

3. GDP of Africa

Give the total GDP of Africa

select SUM(gdp) from world
where continent = 'africa'
SUM(gdp)
1811788000000

4. Count the big countries

How many countries have an area of at least 1000000 ?

select COUNT(name) from world
where area >= 1e6
COUNT(name)
29

5. Baltic states population

What is the total population of ('Estonia', 'Latvia', 'Lithuania')

select SUM(population) from world
where name IN ('Estonia', 'Latvia', 'Lithuania')
SUM(populatio..
6251750

Using GROUP BY and HAVING

6. Counting the countries of each continent

For each continent show the continent and number of countries.

select continent, COUNT(name) as countries
from world
group by continent
continent countries
Africa 53
Asia 47
Caribbean 11
Eurasia 2
Europe 44
North America 11
Oceania 14
South America 13

7. Counting big countries in each continent

For each continent show the continent and number of countries with populations of at least 10 million.

select continent, COUNT(name)
from world
where population >= 10e6
group by continent
continent countries
Africa 29
Asia 26
Caribbean 2
Eurasia 1
Europe 14
North America 4
Oceania 1
South America 8

8. Counting big continents

List the continents that have a total population of at least 100 million.

select continent, SUM(population) as population from world
group by continent
HAVING SUM(population) >= 100e6
ORDER BY population DESC
continent population
Asia 4342955676
Africa 1016091005
Europe 610261850
North America 518755156
South America 407618970
Eurasia 149017400