汇总数据

9.2.1 汇总数据
SQL提供了5个汇总函数用于在表的列上进行算术运算，这5个汇总函数是：Count、Sum、Avg、Max、Min。

Count和Sum尽管在表面上比较相近，但其作用完全不同。

Count 用于计算表中数据的行数，而Sum用于计算数值项相加的和。

Avg用于计算表中一个字段的平均值，Max用于得到最大值，Min用于得到最小值，如表8.7所示。

表8.7 汇总函数列表
当使用汇总函数时，默认情况下，汇总信息包含所有的指定行。

在某些情况下，结果集包含非惟一的行。

可使用汇总函数的DISTINCT 选项筛选出非惟一的行。

任务9.12
问题描述：计算产品表中的行数。

解决方案：
SELECT Count(*)
FROM Products
分析与讨论：
汇总函数Count(*)计算表的行数，包括含有NULL值的行。

注意：除非和ＧROUP BY相连，在查询的项中，SELECT后面的汇总函数不能和字段名一起使用。

如下面的代码是错误的：
SELECT ProductName ,Count(*) FROM Products
任务9.13
问题描述：统计订单数目。

解决方案：
SELECT Count( Distinct OrderID) AS 订单数目
FROM [Order Details]
分析与讨论：
汇总函数Count(OrderID)计算OrderID列的值的个数，OrderID列的NULL值不包含在内。

汇总函数Count( Distinct OrderID) 计算OrderID列的值的个数，OrderID列的NULL值不包含在内，如果多行OrderID值相同，则只取一行参入计数。

考虑下面两个查询语句的不同。

SELECT C ount(OrderID) AS 订单数目 FROM [Order Details]
SELECT Count( Distinct OrderID) AS 订单数目 FROM [Order Details]
第一个查询语句计算订单明细表中所有的订单数，包括重复的订单，第二个查询只计算
不同的订单。

任务9.14
问题描述：查询所有订单的销售额。

解决方案：
SELECT SUM(UnitPrice *Quantity *(1-Discount)) AS 销售额
FROM [Order Details]
分析与讨论：
以上查询使用汇总函数SUM对计算列UnitPrice *Quantity *(1-Discount)求和，求出所有订单的销售额。

9.2.2 分组查询
对于一个查询的输出结果，还可以基于指定的列对它们进行分组操作。

通过使用GROUP BY关键字来对行依照指定列相同的值进行分组，这样就可以实现对每组记录进行计算，并将每一组记录合并或汇集为一个记录。

像这样使用GROUP BY关键字的查询叫做分组查询，分组查询常和汇总函数一起使用。

1.按一列的值分组
按一列的值分组，就是对一列使用GROUP BY，这样列值相同的行归为一组，然后就可以在SELECT 子句<select> 列表中使用汇总函数进行基于组的计算，每一组都汇集为一行。

任务9.15
问题描述：计算图9.1所示的表(a)订单明细表中每个不同订单订购的产品总数。

（a）(b)
图9.1 对OrderID列分组查询
解决方案：
SELECT OrderID, SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
分析与讨论：
要计算出图9.1所示的表(a)每个不同订单订购的产品总数，可对表(a)使用GROUP BY 关键字对OderID列进行分组，DBMS会首先按照OderID值把所有的行排序，然后将所有OderID列值相同的行归成一组，针对于每一个不同的OderID值，都会有相应的一个组，这样就得到6组，然后对各组的Quantity列求和，就得到各组的订购产品的总数，并将每一组都合并为一条记录（如图9.1（b）所示）。

当使用GROUP BY时，只有在GROUP BY子句中出现的列和汇总函数可以出现在SELECT指定的列表中，以下查询会产生错误：
SELECT OrderID, Quantity,SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
2. 按多列的值分组
按多列的值分组，就是对多列使用GROUP BY，这几列值相同的行归为一组。

任务9.16
问题描述：对产品表，计算每一个供应商对每一类别供应多少个不同的产品。

解决方案：
USE Northwind
SELECT SupplierID,CategoryID,COUNT(*)AS 不同产品数
FROM Products
GROUP BY SupplierID,CategoryID
ORDER BY SupplierID
(a) (b)
图9.1对SupplierID和CategoryID分组查询
分析与讨论：
以上代码对SupplierID,CategoryID列分组，这样在Products表中，SupplierID,CategoryID 列值相同的行归为一组，如图9.2(a)所示，1-2行归为第一组，第3行为第二组，4-7行归为第三组，第8、9行归为四组，第7行归为第五组。

第一组有二行，第二有一行，第三组有四行，第五组有一行。

在分组查询结果中，每一组都汇集为一行，汇总函数提供的是有关每个组（而不是各行）的信息（如图9.2(b)）。

当使用GROUP BY指定多列时，列名之间用逗号分隔，不能使用在SELECT 列表中定义的列别名来指定组合列。

在SELECT 列表中所有未包含在汇总函数中的列都应该包含在GROUP BY 子句中。

包含在GROUP BY 子句中的列不一定必须包含在SELECT 列表中。

注意，GROUP BY指定的列不能是类型为text、ntext 和image 的列。

ORDER BY可以应用于分组查询中。

3．使用HAVING筛选分组后的行
HAVING 子句是对GROUP BY 子句设置条件，就是对组设置条件，它和WHERE类似，但WHERE 搜索条件在进行分组操作之前应用；而HAVING 搜索条件在进行分组操作之后应用。

HAVING 语法与WHERE 语法也类似，但HAVING 可以包含汇总函数。

HAVING 子句可以引用选择列表中显示的任意项。

任务9.17
问题描述：查询订购产品数量大于100的的订单的订单号。

解决方案：
SELECT OrderID, SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
HAVING SUM(Quantity)>100
分析与讨论：
以上查询对分组之后的行进行筛选，只有产品总数列的值大于100的行才包含在查询结果中。

如图9.1(b)中，只有第5、6行包含在查询结果中。

当设置HAVING 搜索条件时可以包含汇总函数，但不能使用在SELECT 列表中定义的列别名，如使用以下HAVING 子句是错误的：
HAVING 产品总数>100
使用HAVING 过滤分组:
1）行已经被分组。

2）使用了汇总函数。

3）满足HAVING 子句中条件的组将被显示
4．将WHERE、GROUP BY 和HAVING一起使用
如果一个查询包含WHERE、GROUP BY 和HAVING 子句，应用WHERE、GROUP BY 和HAVING 子句的顺序如下：
1）WHERE 子句用来筛选FROM 子句中指定的操作所产生的行。

2）GROUP BY 子句用来分组WHERE 子句的输出。

3）HAVING 子句用来从分组的结果中筛选行。

对于可以在分组操作之前或之后应用的任何搜索条件，在WHERE 子句中指定它们会更有效，这样可以减少必须分组的行数。

应当在HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。

任务9.17
问题描述：查询单价格超过$150 且平均订定购数量大于 5 的产品
解决方案：
SELECT ProductID ,AVG(Quantity) AS 平均订购数量
FROM "Order Details"
WHERE UnitPrice >= 150.00
GROUP BY ProductID
HAVING AVG(Quantity) > 5
ORDER BY ProductID ;
分析与讨论：
以上分组查询首先选择Order Details表中UnitPrice值大于或等于150.00的行，然后将选择出的行按ProductID的值分组，每一组都汇集为一行，再从分组的结果中，选择AVG(Quantity) 值大于5的行。

也就是说组和汇总值是在消除价格低于$150 且平均订购数量低于或等于5的产品之后得出的。

注意，WHERE 放在GROUP BY的前面，HAVING放在GROUP BY的后面。

不能在WHERE 子句中使用汇总函数，但可以在HAVING 子句中使用汇总函数。