第九课 汇总数据
一、聚集函数
(1)定义:对某些航运行的函数,计算并返回一个值
1.1 AVG
SELECT AVG(age) AS avg_age
FROM inf
WHERE YEAR(birthday) = 2020
ORDER BY id;
1.2 count函数
(1)对age列非NULL的行进行计数
SELECT count(age) AS num_age
FROM inf
WHERE YEAR(birthday) = 2020
ORDER BY id;
(2)对表中行的数目进行统计
SELECT count(*) AS num_cust
FROM inf
WHERE YEAR(birthday) = 2020
ORDER BY id;
1.3 MAX、MIN函数
(1)找出最大值或日期且忽略列值为NULL的行
1.4 SUM函数
二、聚集不同值
(1)定义: DISTINCT 去除重复项再计算
SELECT AVG( DISTINCT age) AS avg_age
FROM inf
WHERE YEAR(birthday) = 2020
ORDER BY id;
三、组合聚合函数
SELECT
AVG( DISTINCT age) AS avg_age,
MAX( age ) AS max_age
FROM inf
WHERE YEAR(birthday) = 2020
ORDER BY id;