Calculate columns and averages in a SQL Server database

Strictly speaking, having does not require a child table, but having no child table has no practical significance. If you only need a table, then you can use the WHERE clause to achieve all of your goals. For practice, having presupposes at least two tables and an aggregate function based on the second table.

Here's a simple example: you want a customer list with a total order of more than 25000 dollars. You need to properly connect the three tables: Customer, SalesOrderHeader and SalesOrderDetail. Then you ask for the sum of the detail and compare the total to 25000 dollars. View List A.

When looking at the code in List A, one thing is not obvious, that is, the LineTotal column is computed. You can find the calculated column's and the same as the actual column. However, you cannot perform two different levels of aggregate functions 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     
by Sales.Customer.CustomerID,     
Sales.SalesOrderHeader.SalesOrder
idhaving SUM (linetotal) > 25000.00ORDER     
by Sales.Customer.CustomerID,     
SalesOrderID;

List A:

Suppose you want to know the average sales for all customers. You can use the code in list B, which returns 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     
by Sales.SalesOrderHeader.SalesOrderID

List B:

MSG 130, 1, line 1
cannot perform a aggregate function on a expression 
containing an aggregate or a subquery.

Error message:

You can solve this problem by decomposing the average calculation process. You can write the first part (SUM) as a table-valued UDF, as shown in Listing C. You can calculate the average based on the function in List D. List e shows how you can make a combination.

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 is     
SET @Result = (sele Ctavg (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 &G T 1 THEN ' Above Average ' when       
totalsale/@Avg < 1 THEN ' Below ' ' ELSE ' ' Average ' ' The From
dbo. SALESTOTALS_FNT ()

List e:

Now you know how to test the aggregate value using a HAVING clause from a child table. When you need to use two different aggregate functions in a query, it is best to break them down into separate functions and then combine them (as explained in the previous example).