ORACLE单行函数与多行函数之六：多行函数之分组函数示例

多行函数：

从一组记录中返回一条记录，可出现在select列表、ORDER BY和HAVING子句中

通常都可用DISTINCT过滤掉重复的记录，默认或用ALL来表示取全部记录

无论是否过滤重复记录，NULL在聚合函数中总是不被计算，被忽略。

主要函数：

–COUNT

–MIN和MAX

–AVG和SUM---只可用于数值型

1.COUNT统计行数函数，及where、group by、having的应用。
NULL值被过滤掉，不计入统计。
BYS@bys1>select count(*), count(comm) from emp;
  COUNT(*) COUNT(COMM)
---------- -----------
        14           4
按部门号分组，统计每个部门的员工数。
BYS@bys1>select deptno,count(empno) from emp group by deptno;
    DEPTNO COUNT(EMPNO)
---------- ------------
        30            6
        20            5
统计每个部门的员工数，不统计 empno=7788的员工
BYS@bys1>select deptno,count(empno) from emp where empno!=7788 group by deptno;
    DEPTNO COUNT(EMPNO)
---------- ------------
        30            6
        20            4
        10            3
统计每个部门的员工数，不统计 empno=7788的员工，统计完后不显示deptno为10的部门。
BYS@bys1>select deptno,count(empno) from emp where empno!=7788 group by deptno having deptno!=10;

    DEPTNO COUNT(EMPNO)
---------- ------------
        30            6
        20            4
2.SUM求和，只可用于数值型
求所有员工工资和
BYS@bys1>select sum(sal) from emp;

  SUM(SAL)
----------
     29025
统计每个部门的工资总和，不统计EMPNO=7788的员工工资，同时统计完后不显示DEPTNO为10的部门。
BYS@bys1>select deptno,sum(sal) from emp where empno!=7788 group by deptno having deptno!=10;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20       7875
3.AVG求平均数，只可用于数值型
BYS@bys1>select avg(sal) from emp;
  AVG(SAL)
----------
2073.21429
BYS@bys1>select deptno,avg(sal) from emp where empno!=7788 group by deptno having deptno!=10;
    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20    1968.75
4.max和min：求最大和最小值
BYS@bys1>select deptno,max(sal),min(sal) from emp where empno!=7788 group by deptno having deptno!=10;
    DEPTNO   MAX(SAL)   MIN(SAL)
---------- ---------- ----------
        30       2850        950
        20       3000        800
BYS@bys1>select max(sal),min(sal) from emp;
  MAX(SAL)   MIN(SAL)
---------- ----------
      5000        800

5.GROUP BY分组，可以指定一列或多列。多列时，先按第一列分组，然后在分组中，如果有符合第二列的，再进行分组。

BYS@bys1>select deptno,job,max(sal),min(sal) from emp where empno!=7788 group by deptno,job having deptno!=10;
    DEPTNO JOB         MAX(SAL)   MIN(SAL)
---------- --------- ---------- ----------
        20 CLERK           1100        800
        30 SALESMAN        1600       1250
        20 MANAGER         2975       2975
        30 CLERK            950        950
        30 MANAGER         2850       2850
        20 ANALYST         3000       3000

6.HAVING，用于在分组后的结果中进行过滤。可以在HAVING中使用分组函数。在WHERE中不行。因为WHERE执行时，尚未分组，而HAVING时，已经分组完成。
求部门平均工资大于2000的部门编号。
BYS@bys1>select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
    DEPTNO   AVG(SAL)
---------- ----------
        20       2175
        10 2916.66667