[打卡]聚合函数（2）

#1.count 计算指定字段在查询结果中出现sele的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*12)
from employees;
#salary是2000还是30000没有关系，因为计算的是个数
SELECT COUNT(1)
from employees;

SELECT COUNT(4)
from employees;

SELECT COUNT(*)
from employees;

#2.计算表中有几条记录 方式1
-- count(*)
-- count(常数)
-- count(具体的字段)不一定对

SELECT COUNT(commission_pct)
from employees;

SELECT commission_pct
from employees
where commission_pct is null;
-- is not null 35
-- is null 72
SELECT 35+72;

SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct
)/COUNT(commission_pct),SUM(commission_pct)/107
from employees;
#直观 感觉

#3.查询公司中平均奖金率
#不对
SELECT AVG(commission_pct)
from employees;
#正确
SELECT AVG(commission_pct)/COUNT(1)
from employees;

#4.查询各个部门的平均工资
-- 以下不对，这是把107个员工看成一个部门
SELECT AVG(salary)
from employees;
-- 正确的
SELECT department_id,AVG(salary),MAX(salary)
from employees
GROUP BY department_id;

#5.查询各个job_id的平均工资
SELECT job_id,AVG(salary)
from employees
GROUP BY job_id;

#查询各个department_id,job_id的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id,job_id;