Calculate columns and averages with SQL Server's HAVING clause

by Arthur Fuller | Jan 22, 2007 8:00:00 AM

Tags: Databases, Arthur Fuller, clause, Microsoft SQL Server, server, SQL Server Newsletter

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

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.

Print/View all Posts Comments on this article

The HAVING clause is used often with one table. mmiller@... | 01/23/07

I agree JustDave | 01/23/07

HAVING artful@... | 01/23/07

HAVING... chapman.tim@... | 01/23/07

Finding duplicates tdmitch | 01/23/07

I agree here, too JustDave | 01/23/07

Not necessarily... chapman.tim@... | 01/23/07

Not to Nitpick artful@... | 01/31/07

re: Finding duplicates jruby | 01/23/07

Scary... DBAdmin | 01/23/07

re: Scary artful@... | 01/23/07

Scary john.parlberg@... | 01/23/07

Unusual for single-table HAVING not to come up in 20 yrs scott.pletcher@... | 09/04/07