SELECT Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderID, SUM(Sales.SalesOrderDetail.LineTotal) AS SubTotalFROM Sales.Customer INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDGROUP BY Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderIDHAVING SUM(LineTotal) 25000.00ORDER BY Sales.Customer.CustomerID, SalesOrderID ;
假设你希望知道所有客户的平均销售额。你可以使用列表B中的代码,它返回如下错误信息:
(本文来源于图老师网站,更多请访问https://m.tulaoshi.com/sqlserver/)SELECT Sales.SalesOrderHeader.SalesOrderID, AVG(SUM(Sales.SalesOrderDetail.LineTotal)) AS AverageFROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDGROUP BY Sales.SalesOrderHeader.SalesOrderID
Msg 130, Level 15, State 1, Line 1Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
你可以将平均值的计算过程进行分解来解决这个问题。你可以将第一部分(SUM)编写成一个表值UDF,如列表C所示。你可以根据列表D中的函数计算平均值。列表E说明你如何能够进行组合。
USE [AdventureWorks]GO/****** Object: UserDefinedFunction [dbo].[SalesTotals_fnt] Script Date: 12/09/2006 11:32:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[SalesTotals_fnt]()RETURNS TABLEAS RETURN (SELECT SalesOrderID, SUM(LineTotal)AS TotalSale FROM Sales.SalesOrderDetail GROUP BY Sales.SalesOrderDetail.SalesOrderID)