Compute columns and averages with SQL Server HAVING clause

In this article, this clause is briefly described, and some code instances are provided, which is the best way to illustrate the use of having clauses.

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 the following list A.

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 ;

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

List B

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:

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

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

List D

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()

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

As with typical programming, each function is focused on one thing. Then you can apply them and continue to apply them over and over again.