Calculate the column and average value in the SQL Server database

Calculate the column and average value in the SQL Server database

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.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 ;

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.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDGROUP BY     Sales.SalesOrderHeader.SalesOrderID

List B:

Msg 130, Level 15, State 1, Line 1Cannot 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 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)

List C:

CREATE FUNCTION [dbo].[AverageSale_fns] (     -- Add the parameters for the function here     -- )RETURNS moneyASBEGIN     -- 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 @ResultEND

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'     ENDFROM 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 ).