【SQL入门系列二】SQLZOO 分组

5 SUM and COUNT

Aggregate functions
SUM, COUNT, MAX, AVG
SUM

//世界总人口
SELECT SUM(population)
FROM world

//Africa的总GDP
SELECT SUM(GDP) FROM world
WHERE continent='Africa'

DISTINCI
去掉SELECT得到的重复项

//列出所有洲
SELECT DISTINCT(continent)
FROM world

ORDER BY
顺序（ASC or DESC）

GROUP BY

//continent和continent中population不小于10 million的国家数量
SELECT continent, COUNT(name) AS numberofcountries
FROM world
WHERE population>=10000000
GROUP BY continent

HAVING
aggregation不出现在WHERE子句中

//population不小于100million的洲
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>= 100000000

小结：
GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
NULL 的行会单独分为一组；