[打卡]QZY的聚合函数

-- COUNT
-- 作用：计算指定字段在查询结果中出现的个数（不包含NULL值）
SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*12)
FROM employees;

-- 计算表中有多少条记录，如何实现
-- 方法①：count(常数)
SELECT COUNT(1)
from employees;

SELECT COUNT(4)
from employees;

-- 方法 ②：count(*)
SELECT COUNT(*)
from employees;

-- 方法 ③：COUNT(具体字段)不一定对
-- 计算具体指定字段出现的个数时，不计算null值
SELECT COUNT(commission_pct)
from employees;

-- 查询表中不是空值的数据
SELECT commission_pct
FROM employees
WHERE commission_pct is NOT NULL;

-- 公式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 AVG(commission_pct)/count(1)
FROM employees;

-- 查询各个部门的平均工资，最高工资
SELECT AVG(salary),MAX(salary)
FROM employees; #（显然不对，这是把所有107个员工看成了1个部门）

-- GROUP BY的使用
SELECT department_id,avg(salary),max(salary)
FROM employees
GROUP BY department_id;

-- 查询各个job_id的平均工资
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前面