[打卡]谷加伟 ——聚合函数

# 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(具体字段)不一定对

# 注意：计算指定字段出现的个数，是不计算UNLL值的
select count(commission_pct)
from employees;

select commission_pct
FROM employees
WHERE commission_pct is not 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;

# GROUR BY的使用

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

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

select AVG(salary),MAX(salary)
from employees;

# 以下为正确做法

select department_id,AVG(salary),MAX(salary)
from employees
GROUP BY department_id;

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

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

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

# 需求：查询各个deparment_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 job_id,department_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

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

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

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

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

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

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

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

# 结论2：GROUP BY 声明在form后面，在WHERE的后面，ORDER BY 的前面，在LTMIT前面