sqlzoo答案5-SUM and COUNT

sql练习：SUM and COUNT - SQLZoo

world表：

1.sum

Show the total population of the world.

world(name, continent, area, population, gdp)

SELECT SUM(population)
FROM world

2.distinct

List all the continents - just once each.

列出所有大陆——每个只列一次。

select distinct continent 
from world

3.

Give the total GDP of Africa

select sum(GDP)
from world
where continent= 'Africa'

4.count

How many countries have an area of at least 1000000

select count(*)
from world
where area >=1000000

5.

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

select sum(population)
from world 
where name in ('Estonia','Latvia','Lithuania')

6.group by

For each continent show the continent and number of countries.

select continent,count(name)
from world a
group by continent

7.

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

million是后面加6个0

为什么要分组呢？因为问的不是总共的数量。是每个大陆上的数量。

不分组的答案是对于所有大陆而言，count（name）一下，满足population>=10000000的都算。

29+26+2+1+14+4+1+8=85

分组的答案是：

对于每个大陆，显示该大陆及拥有至少1000万人口的国家数量。

select continent, count(name)
from world 
where population>=10000000
group by continent

8.having 才可以作用于聚合函数

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

WHERE子句不能直接用于聚合函数，这里存在语法错误，应该使用HAVING子句来替代WHERE子句进行聚合函数的筛选。

select continent
from world
group by continent
having sum(population)>=100000000