ORACLE：分组函数

目录

 一、分组函数

 avg max min sum

count

 二、创建聚组

group by

分组函数误用

1、select语句中有分组函数，相关的列却没有出现在group by中

 2、group by误用别名

 3、过滤组只能使用having，不能使用where

4、分组函数最多嵌套两层

---

 一、分组函数

分组函数：

作用于一组数据，对一组数据返回一个值，常用的有

AVG：平均值

MAX：最大值

MIN：最小值

SUM：求和

COUNT：统计

 avg max min sum

select sal from emp where deptno=10;

       SAL
----------
      2450
      5000
      1300

select max(sal),min(sal),avg(sal),sum(sal) from emp where deptno=10;

  MAX(SAL)   MIN(SAL)	AVG(SAL)   SUM(SAL)
---------- ---------- ---------- ----------
      5000	 1300 2916.66667       8750

count

• count(*)：返回表中行的总数，包括重复与数据列中为null的行，其他分组函数统计都不计算null的行。如comm非空行为4行，其球平均值时是comm之和除以4。
• count(列名)：统计非空的行

select avg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))
----------------
      157.142857

select avg(comm) from emp;

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

1、返回comm列中非空行数
select count(comm) from emp;

COUNT(COMM)
-----------
	  4

2、返回表emp总行数
select count(*) from emp;

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

distinct

count（distinct）

select distinct deptno from emp;

    DEPTNO
----------
	30
	10
	20

select count(distinct deptno)  from emp;

COUNT(DISTINCTDEPTNO)
---------------------
		    3

 二、创建聚组

group by

SELECT column, group_function(column) FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDER BY column]

• 使用group by子句将表中数据分成若干组
• 不能使用列别名

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

    DEPTNO   AVG(SAL)	MAX(SAL)   MIN(SAL)
---------- ---------- ---------- ----------
	10   2916.66667	    5000       1300
	20	 2175	        3000	   800
	30   1566.66667	    2850	   950

#根据多个列进行分组
select deptno,job,avg(sal) from emp group by deptno,job order by deptno;

    DEPTNO JOB	       AVG(SAL)
---------- --------- ----------
	10 CLERK	   1300
	10 MANAGER	   2450
	10 PRESIDENT	   5000
	20 ANALYST	   3000
	20 CLERK	    950
	20 MANAGER	   2975
	30 CLERK	    950
	30 MANAGER	   2850
	30 SALESMAN	   1400

9 rows selected.

分组函数误用

1、select语句中有分组函数，相关的列却没有出现在group by中

select deptno,max(sal) from emp;
select deptno,max(sal) from emp
       *
ERROR at line 1:
ORA-00937: not a single-group group function

 2、group by误用别名

select deptno no ,max(sal) from emp group by no;
select deptno no ,max(sal) from emp group by no
                                             *
ERROR at line 1:
ORA-00904: "NO": invalid identifier

 3、过滤组只能使用having，不能使用where

使用having时，having可以在group by前也可以在其后面 

having是对分组后的结果进行过滤

select deptno,avg(sal) from emp where avg(sal) >2000 group by deptno;
select deptno,avg(sal) from emp where avg(sal) >2000 group by deptno
                                      *
ERROR at line 1:
ORA-00934: group function is not allowed here


select deptno,avg(sal) from emp having avg(sal) >2000 group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
	10 2916.66667
	20	 2175
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;

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

4、分组函数最多嵌套两层

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

MAX(AVG(SAL))
-------------
   2916.66667

select count(max(avg(sal))) from emp group by deptno;
select count(max(avg(sal))) from emp group by deptno
                 *
ERROR at line 1:
ORA-00935: group function is nested too deeply