分组统计函数（组函数及group by子句）

这种函数每次处理多行，给出一个返回值

Avg平均

Sum求和

Max最大

Min最小

Count计数

select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;

SUM(SAL) MIN(SAL) MAX(SAL) AVG(SAL) COUNT(SAL)

-------- ---------- ---------- ---------- ----------

24925 800 5000 2077.08333 12

select min(hiredate),max(hiredate) from emp;

MIN(HIREDATE) MAX(HIREDATE)

------------------- -------------------

1980/12/17:00:00:00 1982/01/23:00:00:00

日期的小为早，大为晚。

select count(*),count(comm) from emp;

COUNT(*) COUNT(COMM)

-------- -----------

12 4

所有组函数，除了count(*)以外，都忽略null值，count是计数，查看有多少行，count(列)是查看该列有多少非空的行。

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

AVG(COMM) AVG(NVL(COMM,0))

---------- ----------------

550 183.333333

求平均的奖金，奖金为非空的人的平均；和大平均，所有的人参加平均，如果奖金为空，就用零来替代。

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

SUM(COMM) COUNT(COMM) COUNT(*)

---------- ----------- ----------

2200 4 12

上面的语法验证了组函数忽略null值。

SQL> select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)

---------------------

3

计算有多少不同的部门代码的个数。

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

DEPTNO SUM(SAL)

------ ----------

30 9400

20 6775

10 8750

按照部门号码分组，同组的进行统计。9i需要排序，10g不要排序。

select sum(sal) from emp group by deptno;

SUM(SAL)

--------

9400

6775

8750

分组的列不在SELECT列表中，这样写有利于子查询，只列出各个部门的工资总和而不显示部门名称。

select deptno,sum(sal) from emp;

select deptno,sum(sal) from emp

*

ERROR at line 1:

ORA-00937: not a single-group group function

这句话不会运行，因为deptno要求每行都显示，而sum要求多行统计后再显示，违反了原则。

在有组函数的SELECT中，不是组函数的列，一定要放在GROUP BY子句中。

select deptno,job,sum(sal) from emp group by deptno,job;

DEPTNO JOB SUM(SAL)

------ --------- ----------

20 CLERK 800

30 SALESMAN 5600

20 MANAGER 2975

30 CLERK 950

10 PRESIDENT 5000

30 MANAGER 2850

10 CLERK 1300

10 MANAGER 2450

20 ANALYST 3000

多列分组，每列都一样的才放到一起进行统计。30号部门中有四个销售。合并成一行了。

select job,avg(sal) from emp group by job;

JOB AVG(SAL)

--------- ----------

CLERK 1016.66667

SALESMAN 1400

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

要求只显示平均工资大于2000的工作。如何过滤掉其它的行？

select job,avg(sal) from emp

where avg(sal)>2000

group by job;

这句话不会运行，WHERE是条件， avg(sal)是结果。

条件中就使用了结果，违反了因果关系。不但ORACLE完成不了您的需求，我们人类的认知阶段就实现不了。

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

JOB AVG(SAL)

--------- ----------

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

Having是在结果中再次筛选。Having一定得出现在group by 子句得后面。不能独立存在。

select deptno,avg(sal) from emp where job='CLERK' group by deptno having

avg(sal)>1000;

Where和having

可以同时出现再一句话中，起作用的时间不同。

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

MAX(AVG(SAL))

-------------

2916.66667

求各个部门平均工资中的最大的。

组函数的嵌套注意要使用GROUP BY子句。

巧用DECODE函数，改变排版方式

select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",

sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",

sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",

sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987",

count(ename) "总人数" from emp;

1980 1981 1982 1987 

---- ---------- ---------- ---------- ----------

1 10 1 0 12