用SQL Server HAVING子句计算列和平均值

SELECT 

Sales.Customer.CustomerID,

Sales.SalesOrderHeader.SalesOrderID, 

SUM(Sales.SalesOrderDetail.LineTotal) AS SubTotal

FROM 

Sales.Customer

INNER JOIN Sales.SalesOrderHeader

ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

INNER JOIN Sales.SalesOrderDetail

ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID

GROUP BY 

Sales.Customer.CustomerID,

Sales.SalesOrderHeader.SalesOrderID

HAVING SUM(LineTotal) > 25000.00

ORDER BY 

Sales.Customer.CustomerID,

SalesOrderID ;
            

SELECT 

Sales.SalesOrderHeader.SalesOrderID, 

AVG(SUM(Sales.SalesOrderDetail.LineTotal)) AS Average

FROM 

Sales.SalesOrderHeader

INNER JOIN Sales.SalesOrderDetail

ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID

GROUP BY 

Sales.SalesOrderHeader.SalesOrderID

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery.

USE [AdventureWorks]
GO

/****** Object: UserDefinedFunction [dbo].[SalesTotals_fnt]

 Script Date: 12/09/2006 11:32:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[SalesTotals_fnt]()

RETURNS TABLE

AS RETURN

(SELECT SalesOrderID, SUM(LineTotal)AS TotalSale

FROM Sales.SalesOrderDetail

GROUP BY Sales.SalesOrderDetail.SalesOrderID)

CREATE FUNCTION [dbo].[AverageSale_fns] 

(

-- Add the parameters for the function here

-- 

)

RETURNS money

AS

BEGIN

-- Declare the return variable here

DECLARE @Result money

-- Add the T-SQL statements to compute the return value here

SET @Result =(SELECTAvg(TotalSale)AS AverageSale

FROM dbo.SalesTotals_fnt())

-- Return the result of the function

RETURN @Result

END
            

DECLARE @Avg money

SELECT @Avg = dbo.AverageSale_fns()

SELECT 

*, @Avg as Average,

TotalSale / @Avg as Ratio,

CASE 

WHEN TotalSale / @Avg > 1 THEN 'Above Average'

WHEN TotalSale / @Avg < 1 THEN 'Below Average'

ELSE 'Average'

END

FROM dbo.SalesTotals_fnt()