SQLZOO：SUM and COUNT

数据表：world

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

Q1 Total world population

Show the total population of the world.

SELECT SUM(population) FROM world

Q2 List of continents

List all the continents - just once each.

SELECT DISTINCT continent FROM world

去掉重复记录 DISTINCT

DISTINCT：过滤掉多余的重复记录只保留一条，但往往用它来返回不重复记录的条数

1.作用于单列

select distinct name from A  ##根据name去重

2.作用于多列

select distinct name, id from A  ##根据name+id两个字段来去重

select distinct xing, ming from B  ##并非对xing和ming两列字符串拼接后再去重，而是分别作用于xing和ming列

3.COUNT+DISTINCT

select count(distinct name) from A	  --name去重后的数目

count使用嵌套查询统计多字段

select count(*) from (select distinct xing, name from B) AS M

Q3 GDP of Africa

Give the total GDP of Africa

SELECT SUM(gdp) FROM world 
WHERE continent='Africa'

Q4 Count the big countries

How many countries have an area of at least 1000000

SELECT COUNT(name) FROM world 
WHERE area>=1000000

Q5 Baltic states population

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

SELECT SUM(population) FROM world 
WHERE name IN ('Estonia', 'Latvia', 'Lithuania')

Q6 Counting the countries of each continent

For each continent show the continent and number of countries.

SELECT continent,COUNT(name) FROM world 
GROUP BY continent

GROUP BY

GROUP BY 根据一个或多个列对结果集进行分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

Q7 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>=10000000 
GROUP BY continent

Q8 Counting big continents

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

SELECT continent FROM world 
GROUP BY continent 
HAVING SUM(population)>=100000000

HAVING 

WHERE子句在聚合前先筛选记录，作用在GROUP BY 子句和HAVING子句前
HAVING子句在聚合后对组记录进行筛选

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value