Oracle-DQL 5- 分组函数（多行函数）

分组函数（多行函数）：
--针对表中的多行数据进行运算，返回一个结果

1.多行函数

--sum() 求和
SELECT SUM(sal) FROM emp;

--avg() 求平均值
SELECT AVG(sal) FROM emp;

--sum 和 avg 只能对数字类型进行运算

--count() 计数，非空值的行数
SELECT COUNT(empno),COUNT(*),COUNT(comm) FROM emp;

--max() 求最大值，min() 最小值
SELECT MAX(sal),MIN(sal) FROM emp;

--max 和 min 可以针对所有数据类型进行运算
SELECT MAX(hiredate),MIN(hiredate) FROM emp;

SELECT MAX(ename),MIN(ename) FROM emp;

--分组函数会忽略空值
SELECT AVG(comm),COUNT(comm) FROM emp;

SELECT SUM(comm)/COUNT(empno),SUM(comm)/COUNT(comm) FROM emp;

--使用nvl函数处理空值
SELECT AVG(NVL(comm,0)),COUNT(NVL(comm,0)) FROM emp;

--可以使用where字句限定分组函数的计算范围
--计算30号部门的平均工资
SELECT AVG(sal) FROM emp
WHERE deptno = 30;

 

2.group by子句
--计算每个部门的人数
SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;

--如果select后面既有普通列又有分组函数，必须使用group by子句
--并且select后面的普通列必须出现在group by后面

--计算每个职位的工资之和
SELECT job,SUM(sal) FROM emp
GROUP BY job;

--多列分组
--查询每个部门各个职位的最低工资
SELECT deptno,job,MIN(sal),COUNT(*) FROM emp
GROUP BY deptno,job
ORDER BY deptno;

--查询每个部门工资超过2000的员工人数
SELECT deptno,COUNT(*) FROM emp
WHERE sal > 2000
GROUP BY deptno;

 

3.having子句
--having子句出现在group by后面
--使用group by不一定使用having，使用having则必须使用group by
--查询部门平均工资高于2000的部门
/*
where 子句中不能出现分组函数
SELECT deptno FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno;
*/
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;

 

4.分组函数的嵌套
SELECT MAX(AVG(sal)) FROM emp
GROUP BY deptno;
--分组函数的嵌套不能和普通列一起查询

SELECT AVG(MAX(sal)) FROM emp
GROUP BY deptno;

 

【练习】
1.查询每个职位的职位名称及平均工资，按平均工资升序排列.
SELECT job,AVG(sal) FROM emp
GROUP BY job
ORDER BY AVG(sal);

2.查询每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。
SELECT deptno,job,AVG(sal) FROM emp
GROUP BY deptno,job
ORDER BY deptno,AVG(sal) DESC;

3.查询每个工资等级所对应的人数
SELECT s.grade,COUNT(e.ename)
FROM salgrade s,emp e
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade
ORDER BY s.grade;

4.查询EMP表，要求写一条sql语句统计出如下图所示结果

10号部门人数 20号部门人数 30号部门人数
------------ ------------ ------------
3 5 6
SELECT deptno,COUNT(*) FROM emp
GROUP BY deptno;

SELECT SUM(DECODE(deptno,10,1,0)) dept10,
SUM(DECODE(deptno,20,1,0)) dept20,
SUM(DECODE(deptno,30,1,0)) dept30
FROM emp;