sqlzoo-练习5——SUM and COUNT

sqlzoo-SUM and COUNT: https://sqlzoo.net/wiki/SUM_and_COUNT

这期主要是 聚集函数（SUM、COUNT、MAX、DISTINCT ）和 分组 （GROUP BY、HAVING ） 的运用，用于数据统计和分析。

1、Show the total population of the world.

SELECT SUM(population) AS sum
FROM world

• 聚集函数可直接用在SELECT 后面，对表格数据处理后返回一个列（可命名），即计算字段。
• SUM(column) 返回该列的所有值的和。

2、List all the continents - just once each.

SELECT DISTINCT(continent) 
FROM world

//学了下面那个GROUP BY 之后，也可以将其分组，那就是每个洲一个了
SELECT continent
FROM world
GROUP BY continent

• DISTINCT(column) 返回该列的不同的值，每个值只有一个。
• DISTINCT(column) 必须指定列。不能DISTINCT(*)。

3、Give the total GDP of Africa

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

4、How many countries have an area of at least 1000000

SELECT COUNT(name) AS big_country
FROM world
WHERE area > 1000000

• COUNT() 用来统计数量

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

SELECT SUM(population) As sum_p
FROM world
WHERE name in ('Estonia','Latvia','Lithuania')

6、For each continent show the continent and number of countries.

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

• GROUP BY 列名 ： 将数据按列分组，每个组内在统计数据。

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

SELECT continent, COUNT(name) As num_c
FROM world
WHERE population > 10000000
GROUP BY continent

• WHERE 判别放在 GROUP BY 之前

8、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 判别放在 GROUP BY 之后
• HAVING 一般是对分组后的组进行筛选。一般是SUM， COUNT等。保留满足条件的组。
• WHERE 一般是对分组前组内的数据进行筛选。组都在，保留满足条件的组内的数据。

---

---

• 一般的 分组查询 的结构：

SELECT  列1,  聚集函数(列2) AS 别名(计算字段)
FROM  表
WHERE   条件1  	//选行
GROUP BY  列1  	//分组
HAVING  条件2	//选组
ORDER BY  列3	//排序

---