分组函数是对一批（一组）数据进行操作（综合）之后返回一个值。这批数据可能是整个表，也可能是按某种条件把该表分成的组。不少专家认为对于管理者或决策者来说综合数据才是最有价值的信息。例如：对于一个大型企业的老总，他/她可能对企业的平均工资很感兴趣，但对该企业中每个员工的具体工资就没什么兴趣了。

Oracle常用的分组函数有以下5个：

COUNT

AVE

SUM

MAX

MIN

下面分别介绍这5个分组函数。

COUNT（{*【DISTINCT|ALL】表达式}

该函数返回查询的行数。

COUNT（*）返回表中所有的行包括空行和重复的行。

例6-2

SQL> SELECT COUNT (mgr)

2 FROM emp;

例6-2结果

COUNT (MGR)

13

所谓经理管理的员工就是mgr不为空（NULL）的员工。因为COUNT（表达式）返回表中所有表达式为非空的行，所以COUNT（mgr）返回表中所有mgr为非空的行，即由经理管理的员工的人数。

AVE函数的格式如下：

AVE（【DISTINCT|ALL】表达式）

该函数返回表达式的平均值。

SUM函数的格式如下：

SUM（DISTINCT|ALL】表达式）

该函数返回表达式的总合。

现在假设您的老板还想知道公司的员工的平均工资和工资总和，您就可以使用例6-3 的查询语句来完成他的要求。

例6-3

SQL> SELECT AVG(sal)"Average Salary",SUM(sal)"Summary",COUNT(sal)"Records"

2 FROM emp;

MAX函数的格式如下：

MAX（【DISTINCTALL】表达式）

该函数返回表达式的最大值。

MIN函数的格式如下：

MIN（【DISTINCT|ALL】表达式）该函数返回表达式的最小值。

如果您的老板现在还想知道公司中员工的最低工资和最高工资，可以使用例6-4的查询语句来完成这一要求。

例6-4

SQL> SELECT MIN(sal) "Lowest Salary",MAX(sal) "Highest Salary"

2 FROM emp;

不像 AVE和SUM函数只能操作数字型数据，MIN和MAX函数不但可用于数字型数据，而且还可以用于字符型数据和日期型数据。例6-5的查询语句就是MIN和MAX函数用于字符型数据的一个例子。

例6-5

SQL> SELECT MIN(job),MAX(job)

2 FROM emp;

假设老板又想知道公司雇用第一个员工的日期和公司雇用最后一个员工的日期，就可以使用例6-6的查询语句来完成这一新要求。

例6-6

SQL> SELECT MIN(hiredate) "First Day", MAX(hiredate) "Last Day"

2 FROM emp;

在前面几节中，我们都是把一个表看成一个大组来处理。我们可以使用GROUP BY子句把一个表化分成若干个组，在一个表中建立多组数据。

GROUP BY子句的应用

如果老板现在又想知道公司中按职位（job）分类，每类员工的平均工资，可以用例6-7 的查询语句来完成这一要求。

例6-7

SQL> SELECT job,AVG(sal) "Average Salary"

2 FROM emp 3 GROUP BY job;

改变GROUP BY子句的排序次序

从例6-7的显示结果可以看出，查询的结果是按GROUPBY子句中列的由小到大的顺序排列（升序排序），这也是使用GROUP BY子句时Oracle默认的排序方式。我们可以用ORDER BY 子句来改变这一默认排序次序。

可以使用例6-8的查询语句来得到按职位（job）分类的每类员工的平均工资，并且显示的结果是按平均工资（average salary）由大到小的顺序排列（降序）。

例6-8

SQL> SELECT job,AVG(sal)"Average Salary"

2 FROM emp

3 GROUP BY job 

4 ORDER BY "Average Salary" DESC;

GROUP BY子句的特殊用法

GROUP BY子句中的列可以不在SELECT列表中。

可以使用例6-9的查询语句来得到按职位（job）分类（job并没有包含在SELECT子句中）的每类员工的平均工资，并且显示的结果是按职位（job）由小到大的顺序排列的（升序）。

例6-9

SQL> SELECT AVG(sal) "Average Salary"

2 FROM emp

3 GROUP BY job;

注意：

如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。

例6-11

SQL> SELECT job,AVG(sal)

2 FROM emp

3 GROUP BY job;

SQL> SELECT job,AVG(sal)

2 FROM emp

3 GROUP BY job

4 ORDER BY deptno;

请问例6-12的查询语句中是否有错误？如果有错误，错在什么地方？

例6-12的查询语句是错的，错在ORDER BY 子句上。因为deptno不在分组函数中，也不在GROUP BY子句中。下面是例6-12的显示结果。

例6-12结果ORDER BY deptno

ERROR 位于第 4 行：

ORA-00979：不是GROUP BY表达式

在Oracle中您可以使用HAVING子句来限制分组函数。现在您可以将例6-13查询语句中的WHERE换成HAVING，如例6-14。

例6-14

SQL> SELECT job,AVG(sal)

2 FROM emp

3 HAVING AVG(sal)>1500

4 GROUP BY job;

当使用了HAVING子句时，Oracle系统处理的顺序如下：

（1）首先对数据行（记录）进行分组；

（2）把所得到的分组应用于分组函数；

（3）最后显示满足HAVING子句所指定条件的结果。

根据以上对HAVING子句的讨论，虽然例6-14的查询语句得到了所需要的结果，但是它与Oracle处理带有HAVING子句的查询语句的逻辑顺序不同。因此最好把HAVING 子句放在GROUPBY子句之后。您可以将上述查询语句修改成如下的SOL语句（例6-15）。

例6-15

SQL> SELECT job,AVG(sal)

2 FROM emp

3 GROUP BY job

4 HAVING AVG(sal)>1500

5 ORDER BY 2;

例6-16

SQL> SELECT MIN(AVG(sal)),MAX(AVG(sal))

2 FROM emp

3 WHERE job NOT LIKE 'PRESI%'

4 GROUP BY job;

例6-16结果

MIN(AVG(SAL))MAX(AVG(SAL))

3000        1037.5

由例6-16显示的结果看出：在公司中最高平均工资约是最低平均工资的3倍。因此若能解雇一些最高平均工资职位（job）的员工而把最高平均工资职位（job）的一些工作分给最低平均工资职位（job）的员工来做，应该能节省不少开销。

在例6-16查询语句中我们使用了两层的分组函数的嵌套。在Oracle系统中嵌套的分组函数计算顺序是由内到外。也就是说，在Oracle系统中按如下的顺序来执行例6-16查询语句：

（1）在emp表中找到所有职位（job）不是以PRESI开头的数据行（记录）；

（2）将这些数据行（记录）按职位（job）分类；

（3）求出每一类的平均工资；

（4）最后求出这些平均工资的最小值和最大值。与单值函数不同，分组函数只能嵌套两层。

注意：尽管分组函数给我们编写SQL语句带来了很大的方便，但是使用起来可能会使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时，因为使用分组函数通常要扫描整个表，如果使用了GROUPBY子句，Oracle还要进行排序。

分组函数的空值问题

除了COUNT（*）以外，其他的分组函数都不处理空值（NULL）

规章广告：3 例6-17

SQL> SELECT AVG(comm) "Average Commission"

2 FROM emp;

例6-17结果

Average Commission

550

但是例 6-17 的结果似乎有些问题，因为所显示的平均佣金明显偏高。其原因是AVG（comm）不包括comm为空值（NULL）的记录行，而在整个公司中只有推销员（SALESMAN）有佣金。所以例6-17显示的结果其实是推销员（SALESMAN）的平均佣金。可以使用例6-18的SQL语句来验证我们的分析。

例6-18

SQL> SELECT AVG(comm)"Average Commission",SUM(comm)"Summary Commission",

2 job, COUNT(comm) "Records"

3 FROM emp

4 GROUP BY job;

例6-19

SQL> SELECT AVG(NVL(comm,0))  "Average Commission"

2 FROM emp;

例6-19结果

Average Commission

157.142857

例6-19显示的结果似乎就是我们所期望的员工的平均佣金。可以使用例6-20的SQL 语句来验证我们的猜测。

例6-20

SQL> SELECT AVG(NVL(comm,0))"Average Commission",

2 SUM(NVL(comm,0))"Summary Commission",

3 job, COUNT(NVL(comm, 0))"Records"

4 FROM emp

5 GROUP BY job;