How to calculate the column and average value in SQLServer

Strictly speaking, Having does not need a subtable, but Having without a subtable does not have practical significance. If you only need one table, you can use the Where clause for all purposes. For practice, Having assumes at least two tables and a aggregate function based on the second table.

The following is a simple example: a list of customers who want to order more than $25000. Three tables to be connected: Customer, SalesOrderHeader, and SalesOrderDetail. Then, calculate the sum of Detail and compare the total number with $25000. View list.

When viewing the code in list A, one thing is not obvious, that is, the LineTotal column is calculated. You can obtain the sum of computed columns just like the sum of actual columns. However, you cannot execute two Aggregate functions at different levels in the same operation.

SELECT

Sales. Customer. CustomerID,

Sales. SalesOrderHeader. SalesOrderID,

SUM (Sales. SalesOrderDetail. LineTotal)

AS SubTotalFROM

Sales. Customer

Inner join Sales. SalesOrderHeader

ON Sales. SalesOrderHeader. Customer

ID = Sales. Customer. CustomerID

Inner join Sales. SalesOrderDetail

ON Sales. SalesOrderDetail. SalesOrder

ID = Sales. SalesOrderHeader. SalesOrderIDGROUP

Sales. Customer. CustomerID,

Sales. SalesOrderHeader. SalesOrder

Idhaving sum (LineTotal)> 25000.00 ORDER

Sales. Customer. CustomerID,

SalesOrderID; List:

Suppose you want to know the average sales of all customers. You can use the code in list B to return the following error message:

SELECT

Sales. SalesOrderHeader. SalesOrderID,

AVG (SUM (Sales. SalesOrderDetail. LineTotal ))

AS AverageFROM

Sales. SalesOrderHeader

Inner join Sales. SalesOrderDetail

ON Sales. SalesOrderDetail. SalesOrder

ID = Sales. SalesOrderHeader. SalesOrder

IDGROUP

Sales. SalesOrderHeader. SalesOrderID List B:

Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression

Containing an aggregate or a subquery. error message:

You can break down the calculation process of the average value to solve this problem. You can write the first part (SUM) into a table value UDF, as shown in list C. You can calculate the average value based on the function in list D. List E describes how you can combine them.

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) List C:

Create function [dbo]. [AverageSale_fns] (

-- Add the parameters for the function here

--) RETURNS money

ASBEGIN

-- 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 List D:

DECLARE @ Avg moneySELECT @ 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 () List E:

Now you know how to use the Having clause to test the total value for a sub-table. When you need to use two different aggregate functions in a query, it is best to break them into separate functions and then combine them (as described in the previous example ).