第9课 SQL入门之汇总数据

本专栏目录

第1课 SQL入门之了解SQL
第2课 SQL入门之检索数据
第3课 SQL入门之排序检索数据
第4课 SQL入门之过滤数据
第5课 SQL入门之高级数据过滤
第6课 SQL入门之用通配符进行过滤
第7课 SQL入门之创建计算字段
第8课 SQL入门之使用数据处理函数
第9课 SQL入门之汇总数据
第10课 SQL入门之分组数据
第11课 SQL入门之使用子查询
第12课 SQL入门之联结表
第13课 SQL入门之创建高级联结
第14课 SQL入门之组合查询
第15课 SQL入门之插入数据
第16课 SQL入门之更新和删除数据
第17课 SQL入门之创建和操纵表
第18课 SQL入门之使用视图
第19课 SQL入门之使用存储过程
第20课 SQL入门之管理事务处理
第21课 SQL入门之使用游标
第22课 SQL入门之高级SQL特性
附录A SQL入门之SQL样例表脚本
附录B SQL入门之SQL流行的应用程序
附录C SQL入门之SQL语句的语法
附录D SQL入门之SQL数据类型
附录E SQL入门之SQL保留字

---

这一课介绍什么是SQL的聚集函数，如何利用它们汇总表的数据。

9.1 聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此SQL提供了专门的函数。使用这些函数，SQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
• 获得表中某些行的和；
• 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。
  上述例子都需要汇总表中的数据，而不需要实际数据本身。因此，返回实际表数据纯属浪费时间和处理资源（更不用说带宽了）。再说一遍，我们实际想要的是汇总信息。
  为方便这种类型的检索，SQL给出了5个聚集函数，见表9-1。这些函数能进行上述检索。与前一章介绍的数据处理函数不同，SQL的聚集函数在各种主要SQL实现中得到了相当一致的支持。

聚集函数（aggregate function） 对某些行运行的函数，计算并返回一个值。

表9-1 SQL聚集函数

函　　数	说　　明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

以下说明各函数的使用。

9.1.1 AVG()函数

AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
下面的例子使用AVG()返回Products表中所有产品的平均价格：
输入▼

SELECT AVG(prod_price) AS avg_price 
FROM Products; 

输出▼

avg_price
6.823333

分析▼
此SELECT语句返回值avg_price，它包含Products表中所有产品的平均价格。如第7课所述，avg_price是一个别名。
AVG()也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格：
输入▼

SELECT AVG(prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01'; 

输出▼

avg_price
3.8650

分析▼
这条SELECT语句与前一条的不同之处在于，它包含了WHERE子句。此WHERE子句仅过滤出vend_id为DLL01的产品，因此avg_price中返回的值只是该供应商产品的平均值。

警告：只用于单个列 AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

说明：NULL值
AVG()函数忽略列值为NULL的行。

9.1.2 COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式：

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
  下面的例子返回Customers表中顾客的总数：
  输入▼

SELECT COUNT(*) AS num_cust 
FROM Customers; 

输出▼

num_cust
5

分析▼
在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在num_cust中返回。
下面的例子只对具有电子邮件地址的客户计数：
输入▼

SELECT COUNT(cust_email) AS num_cust 
FROM Customers;  

输出▼

num_cust
3

分析▼
这条SELECT语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。在此例子中，cust_email的计数为3（表示5个顾客中只有3个顾客有电子邮件地址）。

说明：NULL值
如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但如果COUNT()函数中用的是星号（*），则不忽略。

9.1.3 MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：
输入▼

SELECT MAX(prod_price) AS max_price 
FROM Products; 

输出▼

max_price
11.9900

分析▼
这里，MAX()返回Products表中最贵物品的价格。

提示：对非数值数据使用MAX() 虽然MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。

说明：NULL值
MAX()函数忽略列值为NULL的行。

9.1.4 MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：
输入▼

SELECT MIN(prod_price) AS min_price 
FROM Products; 

输出▼

min_price
3.4900

分析▼
其中MIN()返回Products表中最便宜物品的价格。

提示：对非数值数据使用MIN()
虽然MIN()一般用来找出最小的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MIN()返回该列排序后最前面的行。

说明：NULL值
MIN()函数忽略列值为NULL的行。

9.1.5 SUM()函数

SUM()用来返回指定列值的和（总计）。下面举一个例子，OrderItems包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有quantity值之和）：
输入▼

SELECT SUM(quantity) AS items_ordered 
FROM OrderItems 
WHERE order_num = 20005; 

输出▼

items_ordered
200

分析▼
函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。
SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：
输入▼

SELECT SUM(item_price*quantity) AS total_price 
FROM OrderItems 
WHERE order_num = 20005; 

输出▼

total_price
1648.0000

分析▼
函数SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。

提示：在多个列上进行计算

如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

说明：NULL值
SUM()函数忽略列值为NULL的行。

9.2 聚集不同值

以上5个聚集函数都可以如下使用：

• 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）。
• 只包含不同的值，指定DISTINCT参数。

提示：ALL为默认
ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

说明：不要在Access中使用
Microsoft Access在聚集函数中不支持DISTINCT，因此下面的例子不适合于Access。要在Access得到类似的结果，需要使用子查询把DISTINCT数据返回到外部SELECT
COUNT(*)语句。

下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：
输入▼

SELECT AVG(DISTINCT prod_price) AS avg_price
 FROM Products 
 WHERE vend_id = 'DLL01'; 

输出▼

avg_price
4.2400

分析▼
可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

警告：DISTINCT不能用于COUNT(*)
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

提示：将DISTINCT用于MIN()和MAX()
虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。

说明：其他聚集参数
除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的子集进行计算的TOP和TOP PERCENT。为了解具体的DBMS支持哪些参数，请参阅相应的文档。

9.3 组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数。但实际上，SELECT语句可根据需要包含多个聚集函数。请看下面的例子：
输入▼

SELECT COUNT(*) AS num_items,
        MIN(prod_price) AS price_min,
        MAX(prod_price) AS price_max,
        AVG(prod_price) AS price_avg
 FROM Products; 

输出▼

num_items	price_min	price_max p	rice_avg
9	3.4900	11.9900	6.823333

分析▼
这里用单条SELECT语句执行了4个聚集计算，返回4个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）。

警告：取别名
在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多SQL实现不支持，可能会产生模糊的错误消息

---

上一篇：第8课 SQL入门之使用数据处理函数