Data Management

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.
Automatically sign up today!

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.

13 comments
DBAdmin
DBAdmin

"As an instructor of database language and usage" "HAVING does not require a sub-table, but using it without one is pointless" This writer/instructor scares me. His advice is often very questionable (such as with this article) and his SQL coding often shows very poor performance when compared to alternatives.

tdmitch
tdmitch

Like the above respondent, I disagree with the statement that using HAVING in a single table is useless. The HAVING statement is also very useful in finding a specified number of duplicates for a particular value on a table: SELECT foo_type, COUNT(*) FROM foo_table GROUP BY foo_type HAVING COUNT(*) > 1 The above will return only those values that appear more than once in the table.

mmiller
mmiller

First, thank you for your article. It is very useful and informative and I will keep a copy for reference. However, I disagree with your statement "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". It is not possible to issue: SELECT customer, SUM(amount) AS amount FROM mytable WHERE SUM(amount) > 0 GROUP BY customer The only possible way to do this is: SELECT customer, SUM(amount) AS amount FROM mytable GROUP BY customer HAVING SUM(amount) > 0 Respectfully, Marc Miller

scott.pletcher
scott.pletcher

Since I've seen literally dozens of useful cases of HAVING on a single table over the last few years, including several recent ones, it does seem unusual that it never came up before the author. Perhaps it came up but it just wasn't recognized and so was resolved a different way?

john.parlberg
john.parlberg

I wouldn't be so critical. Yes, there are many ways to solve a particular problem. Having someone who is particularly open about stressing certain examples gets the readers to think a bit. I do appreciate these articles as they are short, to the point, and yet provide meaningful content. Keep writing these useful, but simple articles, and we'll keep providing helpful feedback.

artful
artful

I'm sorry that you think so poorly of my tips. The truth is, I have never yet used HAVING on a single table, even with 20 years in the business. However, several respondents have pointed to cases where they found it useful. I stand corrected. These situations have never come up in my experience. One can only have so much experience. That said, I still think that there is not much useful information to be gained using HAVING with only one table. Surely one would want to pipe this result into a join against another table. So you could write it as a UDF and join it to the Customers table. I agree to that. But assuming that you confine yourself to precisely one table, what do you gain by using HAVING? At best, AFICS, a collection of pointers to another table, which was precisely my point. A.

jruby
jruby

Boing! I've been wrestling with a problem of how to sanitize some external garbage, er... I mean some non-standardized customer data, and your use of HAVING will work nicely. Thanks! Jim /* If you think the problem is bad now, wait until we solve it! */

JustDave
JustDave

I agree with you here to, but someone who is wound too tight is going to point out that this is what a primary key is for. There are cases that you will need to use this though, for example, when you get data from an outside source and need to determine what to use as a primary key without resorting to adding a counter field.

chapman.tim
chapman.tim

When you think about it, it really shouldn't matter if you are running your aggregate query with a HAVING clause on one table, or fifty. You are calculating from an aggregation on a resultset...which is returned before the having clause is even considered. So, it really doesn't matter how many tables (one or fifty) make up the resultset, it is handled the same.

JustDave
JustDave

I use HAVING on a single table quite frequently. One example is to find what department has more than 10 invoices in our invoice table Select Department, Count(Distinct InvoiceNumber) as Invoices From InvoiceTable Group By Department Having Count(Distinct InvoiceNumber) > 10 Order By Invoices Desc

chapman.tim
chapman.tim

...just becuase there are duplicates, doesn't mean that it is a bad thing, or needs any type of constraint. It could be that you just want to run a query to find how many of a product you have sold for products where you have sold over a certain number of products: SELECT prod, count(*) FROM soldprodtable GROUP BY prod HAVING COUNT(*) > 5 This will bring me back all products that have been sold where the product being sold has been sold more than 5 times.

artful
artful

You're quite right. HAVING makes sense on a single table in limited circumstances. Sorry for dismissing this use so cavalierly. Arthur

artful
artful

Your query is an excellent example of using HAVING on a single table. However, I think that determining whether a product has been sold more than 5 times is not exactly data upon which to stake the company. :) I would be more interested in the sum of the quantity column. USE [AdventureWorks] GO SELECT ProductID, SUM(OrderQty)AS [Total Purchases] FROM Sales.SalesOrderDetail GROUP BY [ProductID] HAVING SUM(OrderQty) > 600 So there, everyone. I have just demonstrated my ability to listen and learn. A single-table query using HAVING. Arthur

Editor's Picks