（十一）分组函数(多行函数)

分组函数

多行函数也叫做组函数，分组函数作用于一组数据，并对一组数据返回一个值，

如，AVG,COUNT,MAX,MIN,SUM,操作的是一组数据，返回一个结果，组函数能自动滤空

SQL> select sum(sal) from emp;

  SUM(SAL)
----------
     29025

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select sum(sal)/count(*) from emp;

SUM(SAL)/COUNT(*)
-----------------
       2073.21429

SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429

SQL> select sum(comm)/count(*) from emp;

SUM(COMM)/COUNT(*)
------------------
        157.142857

SQL> select avg(comm) from emp;

 AVG(COMM)
----------
       550

SQL>

统计工种

SQL> select count(distinct job) from emp;

COUNT(DISTINCTJOB)
------------------
                 5

SQL>

组函数都有自动滤空功能（忽略空值）

分组数据

group by

按照group by 后面的表达式将 from 后面的table 进行分组。针对每一组使用组函数

查询部门的平均工资

SQL> select avg(sal) from emp group by deptno;

  AVG(SAL)
----------
1566.66667
      2175
2916.66667

注意：在SELECT列表中所有没有包含在组函数中的列，都必须在group by 的后面出现

SQL> select ename,deptno,avg(sal) from emp group by deptno;
select ename,deptno,avg(sal) from emp group by deptno
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式


已用时间:  00: 00: 00.04
SQL> select ename,deptno,avg(sal) from emp group by deptno,ename;

ENAME          DEPTNO   AVG(SAL)
---------- ---------- ----------
JONES              20       2975
WARD               30       1250
SCOTT              20       3000
KING               10       5000
JAMES              30        950
ALLEN              30       1600
MARTIN             30       1250
BLAKE              30       2850
FORD               20       3000
SMITH              20        800
ADAMS              20       1100
MILLER             10       1300
CLARK              10       2450
TURNER             30       1500

已选择14行。

已用时间:  00: 00: 00.01
SQL> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

已用时间:  00: 00: 00.02

Having

使用having过滤分组：

1.行已经被分组

2.使用了组函数

3.满足having子句中条件的分组将被显示

其语法：

SELECT column,group_function
FROM       table
[WHERE  condition]
[GROUP BY   group_by_expression]
[HAVING     group_condition]
[ORDER BY column]

已写入 file afiedt.buf

  1  SELECT deptno,avg(sal)
  2  FROM       emp
  3  GROUP BY   deptno
  4  HAVING     avg(sal) > 2000
  5* ORDER BY deptno
SQL> /

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
        20       2175

已用时间:  00: 00: 00.02

不能在where子句中使用组函数

可以在having子句中使用组函数

group by 语句的增强

按部门统计各个部门不同工种的工资情况

SQL> ed
已写入 file afiedt.buf

  1  SELECT deptno,job,sum(sal)
  2  FROM       emp
  3  GROUP BY   rollup(deptno,job)
  4* ORDER BY deptno
SQL> /

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

已选择13行。

已用时间:  00: 00: 00.05
SQL>

group by deptno,job + group by deptno + group by null
=group by rollup(deptno,job)