分组统计查询

 

分组统计查询

 

 

范例1：查找出公司每个月支出的月工资的总和

SELECT SUM(sal) FROM emp ;

 

范例2：查询出公司的最高工资、最低工资、平均工资

SELECT AVG(sal), ROUND(AVG(sal),2), MAX(sal), MIN(sal) FROM emp ;

 

范例3：统计出公司最早雇佣和最晚雇佣的雇佣日期

SELECT MIN(hiredate) 最早雇佣日期 , MAX(hiredate) 最晚雇佣日期 FROM emp ;

 

范例4：统计公司中间的工资值

SELECT MEDIAN(sal) FROM emp ;

 

范例5：统计工资的标准差与方差

SELECT STDDEV(sal),VARIANCE(sal) FROM emp ;

 

范例6：统计出公司的雇员人数

SELECT COUNT(empno) , COUNT(*) FROM emp ;

 

范例7：验证COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的使用区别

SELECT COUNT(*) , COUNT(ename) , COUNT(comm) , COUNT(DISTINCT job) FROM emp ;

 

范例8：验证三种COUNT()函数的使用方式

SELECT COUNT(ename) , AVG(sal) , SUM(sal) , MAX(sal) , MIN(sal) FROM bonus ;

 

单字段统计函数

范例9：统计出每个部门的人数

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno ;

 

范例10：统计出每种职位的最低和最高工资

SELECT job , MIN(sal) , MAX(sal) FROM emp GROUP BY job ;

 

 

范例15：求出每个部门平均工资最高的工资

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

 

范例17：统计函数嵌套分析

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno ;

 

范例18：查询每个部门的名称、部门人数、部门平均工资、平均服务年限

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(e.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(e.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;

 

范例19：查询出公司各个工资等级雇员的数量、平均工资。

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 ;

 

 

范例20：统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数。

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 ;

 

范例21：现在要求查询出每个部门的详细信息。

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) max,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, COUNT(e.empno) count,ROUND(AVG(sal),2) avg,SUM(sal) sum,MAX(sal) max,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 ;

 

 

范例22：查询出所有平均工资大于2000的职位信息、平均工资、雇员人数

SELECT job, ROUND(AVG(sal),2) , COUNT(empno) FROM emp GROUP BY job HAVING AVG(sal)>2000 ;

 

范例23：列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

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;

 

范例24：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列

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 ASC ;