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 的行会单独分为一组;