SQL HAVING

Transact-SQL (TSQL) Quick Tips – SQL HAVING clause

Here is a simple example of how the SQL HAVING clause works.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs.

The SQL HAVING clause specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.  I often use this clause to filter duplicate values, which I will demonstrate below.

SYNTAX:  HAVING < search condition >

Example 1

You have the following table, which includes quarterly sales for different products, and you would like to see which products have total sales greater than $30,000

Table

QuarterProductSales
1A10000
1B4000
1C9000
1D2500
1A7500
1B8500
1C7000
1D1500
1A11500
1B2500
1C6500
1D5500
1A3500
1B3000
1C8000
1D4500

SQL Query

Result

SQL HAVING

 

Example 2

You have a table listing account numbers and would like to filter duplicate records

Table

AccountName
1234Adam
2345Beth
3456Carl
1234Adam
4567Alex
4321Brett
6543Ellen
6789Drake
9876Gordon
9843Sue
3467Trent
8723Will
4321Brett
7690Fay
2312Jolene
8843Rae

SQL Query

Result

SQL HAVING

As you can see, the SQL HAVING clause provides useful functionality when you are trying to filter down aggregated queries to a specific condition.  I find these to be very handy when I need to track down duplicate values.

If you would like to post any additional examples, please add them to the comments below.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment