[打卡]陈家昌——聚合函数

#count

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

SELECT count(employee_id)
FROM employees;

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

SELECT count(1)
FROM employees;

SELECT count(*)
FROM employees;
#计算表中有多少条记录，如何实现？
#方式1:count(常数)
#方式2:count(*)
#方式3:count(具体字段)不一定对
#注意：计算指定字段，不计算空值
SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct is null;
#不是空的是35
#空的是72
SELECT 72+35

#AVG
SELECT

SELECT AVG (commission_pct)
FROM employees;



SELECT sum(commission_pct)/count(1)
FROM employees;

#group by
#查询各个部门的平均工资和最高工资
SELECT AVG(salary),MAX(salary)
FROM employees;
#把员工看成一个部门

#正确做法
SELECT AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id;
#不显眼，加deparment_id
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;

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

SELECT AVG(salary)
FROM employees
GROUP BY 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
#眼见不为实，没报错


#结论1：SELECT中出现的非组函数的字段必须声明在group by中
#反之，GROUP BY中声明的字段可以不出现在select中
#结论2，GROUP BY声明在from后面，where的后面，ORDER BY的前面，在limit的前面