Oracle分组统计查询

--分组统计查询


--统计函数


select sum(sal) from emp;


select avg(sal),round(avg(sal),2),max(sal),min(sal) from emp;


select min(hiredate) 最早雇佣日期,max(hiredate) 最晚雇佣日期 from emp;


select median(sal) from emp;


select STDDEV(sal), VARIANCE(sal) from emp;


select count(empno),count(*) from emp;


select count(*),count(ename),count(comm),count(distinct job) from emp;


select count(ename),avg(sal),sum(sal),max(sal),min(sal) from bonus;


--单字段分组统计


select deptno,count(*) from emp group by deptno;


select job,min(sal),max(sal) from emp group by job;


select count(empno) from emp;


select deptno,count(empno) from emp group by deptno;


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


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


select d.dname,e.empno,e.ename,e.sal,e.hiredate
from dept d,emp e
where e.deptno= d.deptno;


select d.dname,count(e.empno),round(avg(sal),2) avgsal,
round(avg(months_between(sysdate,e.hiredate)/12),2) avgyear
from dept d,emp e
where e.deptno=d.deptno
group by d.dname;


select d.dname,count(e.empno),round(avg(sal),2) avgsal,
round(avg(months_between(sysdate,e.hiredate)/12),2) avgyear
from dept d,emp e
where e.deptno(+)=d.deptno
group by d.dname;


select s.grade,e.empno,e.sal
from emp e,salgrade s 
where e.sal between s.losal and s.hisal;


select s.grade,count(e.empno),round(avg(e.sal),2)
from emp e, salgrade s 
where e.sal between s.losal and s.hisal
group by s.grade;


select '领取佣金',round(avg(sal),2) avgsal,
round(avg(months_between(sysdate, hiredate)/12),2) avgyear,
count(empno) count
from emp
where comm is not null;


select '不领取佣金',round(avg(sal),2) avgsal,
round(avg(months_between(sysdate, hiredate)/12),2) avgyear,
count(empno) count
from emp
where comm is null;


select '领取佣金',round(avg(sal),2) avgsal,
round(avg(months_between(sysdate, hiredate)/12),2) avgyear,
count(empno) count
from emp
where comm is not null
union
select '不领取佣金',round(avg(sal),2) avgsal,
round(avg(months_between(sysdate, hiredate)/12),2) avgyear,
count(empno) count
from emp
where comm is null;


--多字段分组统计


select d.deptno,d.dname,d.loc,e.empno,e.ename
from emp e,dept d
where e.deptno= d.deptno;


select d.deptno,d.dname,d.loc,
count(e.empno) count,round(avg(sal),2) avg,sum(sal) sum,max(sal),min(sal) min
from emp e,dept d
where e.deptno(+)= d.deptno
group by d.deptno,d.dname,d.loc;


select d.deptno,d.dname,d.loc,
nvl(count(e.empno),0) count,
nvl(round(avg(sal),2),0) avg,
nvl(sum(sal),0) sum,
nvl(max(sal),0) max,
nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)= d.deptno
group by d.deptno,d.dname,d.loc;


--HAVING字句


select job,round(avg(sal),2),count(empno)
from emp 
group by job
having avg(sal)>2000;


select d.deptno,d.dname,e.empno,e.sal
from emp e,dept d
where e.deptno(+)=d.deptno;


select d.deptno,d.dname,round(avg(e.sal)),min(e.sal),max(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;


select  d.deptno,d.dname,round(avg(e.sal),2),min(e.sal),max(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc
having count(e.empno)>1;


select * 
from emp
where job!='SALESMAN';


select job,sum(sal) sum
from emp
where job!='SALESMAN'
group by job;


select job,sum(sal) sum
from emp
where job!='SALESMAN'
group by job
having sum(sal)>5000;


select job,sum(sal) sum
from emp
where job!='SALESMAN'
group by job
having sum(sal)>5000
order by sum(sal) asc;