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 | select count(name) from world |
- What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’)
3个国家的总人口sum
1 | select sum(population) from world |
- For each continent show the continent and number of countries.
每个地区continent有多少个国家count
1 | select continent, count(name) -- 统计总数 |
- For each continent show the continent and number of countries with populations of at least 10 million.
加上where条件再进行分组
1 | select continent, count(name) |
- List the continents that have a total population of at least 100 million.
having是对分组之后的结果进行筛选
1 | select continent |
Group By and Having
select子句顺序
- select
- from
- where
- group by
- having
- order by
习题
- For each continent show the number of countries
统计每个洲中国家的数量
- 洲分组
- 统计数量count
1 | select continent, count(name) |
- For each continent show the total population
统计每个洲的人口总数
- 洲分组
- 统计总数sum
1 | select continent, sum(population) -- 人口求和 |
- For each relevant continent show the number of countries that has a population of at least 200000000.
where语句在group by之前
- 每个地区的国家总数
- 人口需要大于20000000
1 | select continent,count(name) |
- 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
1 | select continent, sum(population) -- 统计总人口 |