注意:聚合函数只返回一个结果
1, 常见的聚合函数
AVG() / SUM()平均值 / 总和
SUM不计算NULL
只适用于数值类型,
例如:
AVG(last_name)——>无意义操作
MAX / MIN最大 / 最小
适用于数值类型,字符串类型,日期类型
所以:
MAX(last_name)——>有意义
COUNT计算指定字段在查询结果中出现的个数,有多少行
SELECT COUNT(employee_id),
COUNT(1),COUNT(*)——>有意义,相当于增加一个常量,行数不变
FROM employees
总结:
A,COUNT(1),COUNT(*)——>一定对
COUNT(具体字段)——>不一定对,因为COUNT不计算NULL
B,AVG(salary) = SUM(salary)/COUNT(salary)
C,应用:查询平均salary
错误:SELECT AVG(salary)——>AVG没有计算工资为NULL的员工
FROM employees;
正确:SELECT SUM(salary)/COUNT(IFNULL(salary,0))
或者
AVG(IFNULL(salary,0))
FROM employees;
2, GROUP BY
用法:调查各个office_id的平均工资
SELECT office_id,AVG(salary)
FROM employees
GROUP BY office_id
用法:查询各个office_id,job_title的平均工资
SELECT office_id,AVG(salary),job_title
FROM employees
GROUP BY office_id,job_title
注意:SELECT中出现的非函数组字段必须分组
3, HAVING过滤分组后的数据
a,过滤条件中使用了聚合函数,必须使用HAVING
b,和groupby联结使用
用法:查询各个office_id最高的salary,筛选大于80000的
SELECT office_id,MAX(salary)
FROM employees
GROUP BY office_id
HAVING MAX(salary) > 80000
用法:查询office_id=1,2,3的最高的salary,筛选大于80000的
SELECT office_id,MAX(salary)
FROM employees
WHERE office_id IN(1,2,3)效率更高
GROUP BY office_id
HAVING MAX(salary) > 80000 #AND office_id IN(1,2,3)
练习
1,查询各个office_id的salary最大值,最小值,平均值,总和
SELECT office_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY office_id
2,查询所有office的地址,office_id,员工数量和平均工资,按平均工资降序排序
SELECT E.'office_id',O.'address',E.'salary',SUM(1),AVG(1)
FROM employees AS E JOIN offices AS O
ON E.'office_id' = O.'office_id'
GROUP BY E.'office_id'
ORDER BY AVG (E.'salary') DESC