《SQL必知必会》学习笔记——第九课 汇总数据

 

第九课  汇总数据

一、聚集函数

（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;