[分享]聚合函数_COUNT和GROUP BY

# COUNT

# 作用：计算指定字段在查询结果中出现的个数

SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*12)
FROM employees;

SELECT COUNT(1)
FROM employees;

SELECT COUNT(4)
FROM employees;

SELECT COUNT(*)
FROM employees;

#计算表中有多少条记录，如何实现？

# 方式1：COUNT(常数)

# 方式2：COUNT(*)

# 方式3：COUNT(具体字段) 不一定对

#注意：计算指定具体字段出现的个数时，是不计算NULL值的

SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NULL;

# 不为空是 35
# 空的是 72

SELECT 72+35

#公式：AVG = SUM / COUNT

SELECT AVG(salary),SUM(salary)/COUNT(salary),

AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),

SUM(commission_pct)/107

FROM employees;

# 直观的感觉

# 需求：查询公司中平均奖金率

# 不对的

SELECT AVG(commission_pct)
FROM employees;

# 换一个场景：查询每一个中国人的平均工资是多少，其中有两亿人没有工作，大家说，这两亿人要不要计算在内？

# 以下正确

SELECT SUM(commission_pct)/COUNT(1)
FROM employees;

# GROUP BY的使用

# 需求：查询各个部门的平均工资，最高工资

# 显然不对，因为这是把107个员工看成了1个部门

SELECT AVG(salary),MAX(salary)
FROM employees;

# 以下为正确做法

SELECT  AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id;

# 需求：查询各个job_id的平均工资

# 比如50号部门，各个工种的工资

SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

# 需求：查询各个department_id,job_id的平均工资

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

-- ------------------------

# 错误的，眼见不一定为实，但是没有报错

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

# 结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY中

# 反之，GROUP BY中声明的字段可以不出现在SELECT中

# 结论2：GROUP BY 声明在from后面,WHERE的后面,ORDER BY的前面,在LIMIT前面