第六章：Oracle分组函数与group by

常见的分组函数：max(),min(),sum(),avg(),count()

组函数最多只能嵌套两层

1、max()和min()函数
对任何类型数据都可以使用，显示最大值和最小值

select max(sal) 最大工资,min(sal) 最小工资 from emp;
 
  最大工资   最小工资
---------- ----------
      5000        800
2、count()函数
count()函数可以用于查询表中记录数，count(列名)会忽略空值，count(*)会返回记录总数（不忽略空值）。

select count(*) 总记录,count(mgr) 有领导数量 from emp;
 
    总记录 有领导数量
---------- ----------
        14         13
3、sum()和avg()函数
avg()函数会自动忽略空值

select avg(comm) "avg" , sum(comm)/count(*) "sum/count" from emp;
 
       avg  sum/count
---------- ----------
       550 157.142857
4、group by(查询语句中使用到了分组函数时)
(1)使用分组函数与其他列名在一起查询的时候必须使用group by子句进行分组

select empno ,avg(sal) from emp
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数
(2)select列表中除了组函数外，所有列都必须包含在group by子句中

select empno,ename ,avg(sal) from emp group by ename
       *
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
 
select empno,ename ,avg(sal) from emp group by ename,empno;
 
     EMPNO ENAME        AVG(SAL)
---------- ---------- ----------
      7698 BLAKE            2850
      7934 MILLER           1300
(3)group by子句中不能使用列的别名

select ename name ,avg(sal) from emp group by name
                                              *
第 1 行出现错误:
ORA-00904: "NAME": 标识符无效
(4)group by子句中可以出现在其他不包含在select列表中的字段

select ename ,sum(sal) from emp group by ename,empno;
 
ENAME        SUM(SAL)
---------- ----------
BLAKE            2850
MILLER           1300
(5)使用having子句进行限制

不能在where子句中使用组函数，只能在having子句中使用。

select ename,avg(sal) from emp where avg(sal)>3000 group by ename;
select ename,avg(sal) from emp where avg(sal)>3000 group by ename
                                     *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数
 
select ename,avg(sal) from emp group by ename having avg(sal)>3000;
 
ENAME        AVG(SAL)
---------- ----------
KING             5000
(6)可以在order by子句中使用组函数

select ename,avg(sal) from emp group by ename order by  avg(sal);
 
ENAME        AVG(SAL)
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100