Calculate columns and averages with SQL Server's HAVING clause

Arthur Fuller gives a brief overview of SQL Server's HAVING clause and provides code examples that demonstrate how to use it. For instance, he shows how to use HAVING to test aggregates based on a child table.

As an instructor of database language and usage, I find some
topics particularly difficult to explain. One of those subjects is SQL Server's
HAVING clause. In this tip, I offer a brief overview of the clause and then
provide code examples, which I think best convey the proper usage of HAVING.

Strictly speaking, HAVING does not require a sub-table, but
using it without one is pointless. If all you need is one table, then you could
do whatever you wish in the WHERE clause. For practical purposes, HAVING presupposes
at least two tables and an aggregate function based on the
second table.

Get SQL tips in your inbox

TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Here's a simple example: You want a list of the Customers
who placed Orders with totals that are greater than $25,000. You will need
three tables that are appropriately joined: Customer, SalesOrderHeader,
and SalesOrderDetail. Then, you will sum the Details
and compare the total against $25,000. View
Listing A.

One thing that is not readily apparent by looking at the
code in Listing A is that the column LineTotal is
computed. You can aggregate computed columns just as you can actual columns.
You cannot, however, perform two aggregates at different levels within the same
operation.

Let's suppose that you want to know the value of the
average sale across all customers. You might try using the code in Listing B. This results in 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 get around this problem by breaking out the
computation of the average. You should code the first part (the SUM) as a
table-valued UDF, as shown in
Listing C. You will base your calculation of the average on the function in
Listing D.
Listing E shows
how you can combine them.

Now you know how to use the HAVING clause to test aggregates based on a child table. When you need two different
aggregates in one query, then it's best to break them out into separate functions,
which you can combine (as illustrated in the last query).

As with classical programming, make each function do
precisely one thing. Then, you can use and reuse.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.