[打卡]COUNT与GROUP BY的使用——sql

#count
#作用：计算指定字段在查询结果中出现sele的个数
SELECT COUNT(employee_id),COUNT(salary)
FROM employees;

#salary是2000还是30000没有关系，只计算个数
SELECT COUNT(salary*12)
FROM employees;

SELECT COUNT(1)
FROM employees;

SELECT COUNT(*)
FROM employees;

#计算表中有多少条记录
#方式一：COUNT（*）
#方式二：COUNT（常数）

#方式三：COUNT（具体字段）——（不一定对）
#注意：计算指定字段出现的个数时，是不计算NULL值的
#例：
SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#IS NOT NULL 35
#IS NULL 72

#AVG
#公式：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 的使用
#需求：查询各部门的平均工资

#错误示范
SELECT AVG(salary)
FROM employees;

#正确示范
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 department_id；

#结论1：SELECT中出现的非组函数字段必须声明在GROUP BY 中
#反之，GROUP BY 中声明的字段可以不出现在select里面

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