1.1.4　分组函数与分组统计

1.分组函数

分组函数又称为聚集函数，是一种多行函数。之所以称为多行函数，是与单行函数对应的，因为这种函数对多行数据一起进行计算，只返回一个结果，而不是每行都返回一个结果。

聚集函数主要用来进行数据的统计，常用的聚集函数有以下几个：

·AVG求平均值

·MIN求最小值

·MAX求最大值

·SUM求和

·COUNT计数

（1）AVG函数

AVG函数用来求指定列上的平均值，它将自动忽略列上的空值。如果要去掉重复值的计算，可在列名前加上DISTINCE选项。例如，要求部门30的员工的平均工资，构造的SELECT语句如下：

SQL＞SELECT avg（sal），avg（distinct sal）FROM emp WHERE deptno=30；

为了观察重复值对这个函数的影响，在SELECT语句中进行了两种形式的函数调用，其中第二次调用去掉了重复值，对重复值只计算一次。这条语句的执行结果为：

AVG（SAL）AVG（DISTINCTSAL）

1566.66667 1630

（2）MIN与MAX函数

MIN函数的作用是求指定列的最小值，MAX函数的作用是求指定列的最大值。这两个函数都自动忽略空行。例如，要求部门30的员工的最低工资和最高工资，构造的SELECT语句如下：

SQL＞SELECT min（distinct sal），max（sal）FROM emp WHERE deptno=30；

这条SELECT语句的执行结果为：

MIN（DISTINCTSAL）MAX（SAL）

950 2850

（3）COUNT函数

COUNT函数用来计算数据的行数。在默认情况下，这个函数不计算空行。如果要计算空行，可以用“*”代替列名。如果要去掉重复值的计算，可在列名前加上DISTINCE选项，这样如果遇到重复值，只计算一次。例如，要计算公司中领取工资的人数，构造的SELECT语句如下：

SQL＞SELECT count（sal），count（distinct sal）FROM emp；

为了观察DISTINCE选项的作用，在SELECT语句中进行了两次函数调用。如果有两个员工的工资相同，只按一个人计算。这条语句的执行结果为：

COUNT（SAL）COUNT（DISTINCTSAL）

12 11

（4）SUM函数

SUM函数的作用是对指定列求和，它将自动忽略空值。如果要去掉重复值的计算，可在列名前加上DISTINCE选项。例如，要求部门30的员工工资总和，构造的SELECT语句如下所示：

SQL＞SELECT sum（sal）FROM emp WHERE deptno=30；

SUM（SAL）

9400

现在，为了说明这些函数的用法，把它们综合起来，构造一个SELECT语句，求部门30的员工的平均工资、最高工资、最低工资、工资总和以及总人数。这条SELECT语句如下所示：

SQL＞SELECT avg（sal）AS平均工资，min（sal）AS最低工资，

max（sal）AS最高工资，sum（sal）AS工资总和

FROM emp

WHERE deptno=30；

这条SELECT语句的执行结果为：

平均工资　最低工资　最高工资　工资总和

1566.66667　950　2850　9400

2.分组统计

在上面最后一个例子中。我们对部门30的员工的工资进行了统计，用这种方法也可以统计其他部门的数据。但是每进行一次统计，都需要单独构造一条SELECT语句，如果表中的部门很多，或者部门数很难确定，用这种方法很难满足用户的查询要求。解决这个问题的一个办法是使用GROUP子句。

分组函数最常见的用法是与GROUP子句一起使用，用来对表中的数据进行分组统计。为了统计表中各个部门员工的工资，只要一条语句就可以完成。GROUP子句的语法格式为：

GROUP BY列1，列2……

GROUP子句根据指定的列对数据进行分组统计。首先根据第一个列进行分组统计，第一个列相同时再进一步根据第二个列进行分组统计。例如，要对公司各部门的员工工资进行统计，包括各部门的平均工资、最高工资、最低工资、工资总和和总人数，构造的SELECT语句如下所示：

SQL＞SELECT deptno AS部门号，avg（sal）AS平均工资，min（sal）AS最低工资，

max（sal）AS最高工资，sum（sal）AS工资总和

FROM emp

GROUP BY deptno；

这样我们就可以用一条语句完成所有部门的统计。这条SELECT语句的执行结果为：

部门号　平均工资　最低工资　最高工资　工资总和

10　2916.66667　1300　5000　8750

20　2258.33333　800　3000　6775

30　1566.66667　950　2850　9400

与GROUP子句一起使用的还有一个子句，即HAVING子句。这个子句是可选的，它不能单独使用，只能配合GROUP子句使用，作用是对GROUP子句设置条件，对统计后的结果进行限制。例如，对于上述统计，我们只希望显示最低工资在900元以上，并且工资总和在7000元以上的部门的统计信息，相应的SELECT语句为：

SQL＞SELECT deptno AS部门号，avg（sal）AS平均工资，min（sal）AS最低工资，

max（sal）AS最高工资，sum（sal）AS工资总和

FROM emp

GROUP BY deptno

HAVING min（sal）＞900 AND sum（sal）＞7000

这样，部门20因为统计信息不满足设置的条件，就不被显示。这条SELECT语句的执行结果为：

部门号　平均工资　最低工资　最高工资　工资总和

10　2916.66667　1300　5000　8750

30　1566.66667　950　2850　9400

HAVING子句中的关系表达式必须使用分组函数，可以是在SELECT语句中已经出现的分组函数，也可以是没有出现的函数。虽然HAVING子句和WHERE子句都是用来设置条件的，但是WHERE子句设置的条件是在查询时起作用的，它决定查询什么样的数据，如果要进行统计，这样的条件是在统计之前就已经起作用了。而HAVING子句设置的条件只有在进行统计后才起作用，它决定了对于统计产生的数据，哪些需要显示给用户。