05、Oracle使用分组函数汇总数据_oracle分组统计总数

1.1 【常用分组函数类型】

1.2 【分组函数基本语法】

1.3 【使用 avg、 max、 min、 sum 函数】

需求：查询 **emp **表销售人员工资的平均值、最大值、最小值、工资总和

-- 查询 emp 表销售人员工资的平均值、最大值、最小值、工资总和
SCOTT@orcl>select avg(sal),max(sal),min(sal),sum(sal) from emp;
1
2

日期类型、字符类型列不能使用 avg、 **sum **函数，可以使用 max、 **min **函数

1.4 【使用 COUNT 函数】

需求： 查询 **30 **号部门的总人数

-- 查询 30 号部门的总人数
SCOTT@orcl>select * from emp where deptno=30;

SCOTT@orcl>select count(*) from emp where deptno=30;
1
2
3
4

SCOTT@orcl>select count(empno) from emp where deptno=30;

SCOTT@orcl>select count(comm) from emp where deptno=30;
1
2
3

【说明】count(comm)： 不统计含有空值的行；COUNT(*)返回表中行的总数， 包括重复行与数据列中含有空值的行， 而其他分组函数的统计都不
包括空值的行。

1.4.1 count(distinct)

SCOTT@orcl>select distinct mgr from emp;

SCOTT@orcl>select count(distinct mgr) from emp;
1
2
3

1.5 【分组函数和空值】

SCOTT@orcl>select count(comm) from emp;
1

需求：查询所有 **14 **名员工的平均奖金

-- 查询所有 14 名员工的平均奖金
SCOTT@orcl>select avg(comm) from emp;
1
2

SCOTT@orcl>select avg(nvl(comm,0)) from emp;
1

1.6【group by 创建聚组数据】

计算每个部门的平均工资,需要指定分组的列。

1.6.1 【group by 子句基本语法】

1.7 【使用 group by 子句】

SCOTT@orcl>select avg(sal) from emp;

SCOTT@orcl>select deptno,avg(sal) from emp group by deptno;
1
2
3

SCOTT@orcl>select deptno,avg(sal) from emp;
1

**select **语句中，没有使用分组函数的列必须在 **group by **子句中

SCOTT@orcl>select avg(sal) from emp group by deptno;
1

**group by **后面的列可以不出现在 **select **语句中

SCOTT@orcl>select deptno dnum,avg(sal) from emp group by dnum;
1

**group by **后面不允许使用列的别名

1.8 【根据多个列进行分组】

需求：查询每个部门中，每种工作的工资总和。

-- 查询每个部门中，每种工作的工资总和。
SCOTT@orcl>select deptno,job,sum(sal) from emp
  2  group by deptno,job order by deptno;
1
2
3

1.9 【分组函数的误用】

SCOTT@orcl>select deptno,count(ename) from emp;
1

**select **语句中有分组函数，没有在分组函数中出现的列必须在 **group by **子句中

SCOTT@orcl>select deptno,avg(sal) from emp
  2  where avg(sal) >2000
  3  group by deptno;
1
2
3

不能在 **where **子句中对聚组列作出限定，应使用 **having **子句

1.10 【having 排除聚组结果】

SCOTT@orcl>select deptno,avg(sal) from emp
  2  having avg(sal)>2000
  3  group by deptno;
1
2
3

需求：查询部门最高工资大于 **2900 **的部门

-- 查询部门最高工资大于 2900 的部门
SCOTT@orcl>select deptno,max(sal) from emp
  2  group by deptno
  3  having max(sal)>2900;

SCOTT@orcl>select deptno,max(sal) from emp
  2  having max(sal)>2900
  3  group by deptno;
1
2
3
4
5
6
7
8

**having **子句可以放在 **group by **子句前面。

1.11 【分组函数的嵌套使用】

SCOTT@orcl>select max(avg(sal)) from emp group by deptno;
1

分组函数最多嵌套两层**(两个函数)**，嵌套时必须有 group by

SCOTT@orcl>select count(max(avg(sal))) from emp group by deptno;
1

嵌套三层失败

【总结】

分组函数： avg/max/min/sum/count
分组函数（聚合函数）要处理空值；select 子句中没有被分组函数包括的列，必须出现在 group by 后面；group by 子句中的列可以不出现在 select 语句中；group by 子句中不能使用列的别名；
如果对分组函数做限制，不能用 where,需要用 having，having 子句可以写在 group by 子句前；
分组函数最多嵌套两层（两个函数）。