sqlzoo练习7-聚合

sqlzoo练习7

主要涉及到的知识点是聚合函数：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

sum求和
count统计数量
max、min 求出最值
distinct去重功能

练习

Show the total population of the world.

1	select sum(population) from world;

List all the continents - just once each.

列出每个不同的洲，使用distinct去重

1	select distinct(continent) from world;

Give the total GDP of Africa

计算非洲的总gdp，sum求和

1	select sum(gdp) from world where continent='Africa';

How many countries have an area of at least 1000000

统计count多少个国家的面积大于1000000

1 2	select count(name) from world where area > 1000000;

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

3个国家的总人口sum

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

For each continent show the continent and number of countries.

每个地区continent有多少个国家count

1
2
3

select continent, count(name)  -- 统计总数
from world
group by continent;  -- 地区分组

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

加上where条件再进行分组

select continent, count(name)
from world
where population >= 10000000  -- 先用where进行筛选
group by continent;

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

having是对分组之后的结果进行筛选

select continent
from world
group by continent  -- 先分组再进行筛选
having sum(population) >= 100000000;

Group By and Having

select子句顺序

select
from
where
group by
having
order by

习题

For each continent show the number of countries

统计每个洲中国家的数量

洲分组

统计数量count

1
2
3

select continent, count(name)
from world
group by continent;  -- 分组

For each continent show the total population

统计每个洲的人口总数

洲分组

统计总数sum

1
2
3

select continent, sum(population)  -- 人口求和
from world
group by continent;

For each relevant continent show the number of countries that has a population of at least 200000000.

where语句在group by之前

每个地区的国家总数

人口需要大于20000000

select continent,count(name)
from world
where population > 20000000
group by continent;

Show the total population of those continents with a total population of at least half a billion.

The HAVING clause is tested after the GROUP BY

select continent, sum(population)   -- 统计总人口
from world
group by continent
having  sum(population) > 500000000;   -- 总人口满足的条件