How to compute columns and averages in SQL Server

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, level, State 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

DECLARE @Result Money

--ADD the T-SQL statements to compute "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 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).