oracle 分组大于平均数,Oracle_SQL(2) 分组与聚合函数

一、聚合函数

1.定义：对表或视图的查询时，针对多行记录只返回一个值的函数。

2.用途：用于select语句，HAVING条件

二、5种聚合函数

1.SUM(n) 对列求和

select sum(sal) from emp;

select deptno,sum(sal) from emp group by deptno;

2.AVG(n) 对列求平均值

select avg(sal) from emp;

select deptno,avg(sal) from emp group by deptno;

3.MAX(n) 对列求最大值

select max(sal) from emp;

select deptno,max(sal) from emp group by deptno;

4.MIN(n) 对列求最小值

select min(sal) from emp;

select deptno,min(sal) from emp group by deptno;

5.COUNT(1) 记录行数

select COUNT(1) from emp;

select deptno,COUNT(1) from emp group by deptno;

select deptno,avg(sal),sum(sal)/COUNT(1) from emp group by deptno;

count(可空列)对null值不计数

select count(comm) from emp;

count(distinct 列)对null值不计数

select count(distinct empno),count(distinct deptno) from emp;

三、分组语句

语法：group by 列名,... having 条件

1.按部门分组，输出5种聚合函数，并按部门排序

select deptno,sum(sal),avg(sal),count(1),max(sal),min(sal)

from emp group by deptno order by deptno;

2.按部门分组，输出部门人数大于3的部门编号及5种聚合函数，并按部门排序

select deptno,sum(sal),avg(sal),count(1),max(sal),min(sal)

from emp group by deptno having count(1)>3 order by deptno;

3.按部门分组，输出部门人数大于等于3并且小于等于6

的部门编号及5种聚合函数，并按部门排序

select deptno,sum(sal),avg(sal),count(1),max(sal),min(sal)

from emp group by deptno having count(1)>=3 and count(1)<=6

order by deptno;

4.按部门分组，输出工资大于1000，部门人数大于等于3并且小于等于6

的部门编号及5种聚合函数，并按部门排序

select deptno,sum(sal),avg(sal),count(1),max(sal),min(sal)

from emp where sal>1000

group by deptno having count(1)>=3 and count(1)<=6

order by deptno;

原文：https://www.cnblogs.com/BradMiller/p/9279501.html