TSQL Having clause

In the spirit of all of the aggregates for reporting I have been doingh lately the next item in the TSQL language that it made sense to discuss is the "having" clause. I had used previously but it was for a different purpose. having is a good way of using the count() function to show duplicates in a dataset. Here is an example:

-- table var
declare @duplicate table
(
col1 int,
col2 varchar(10)
)

-- inserts
insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(2,'two')

insert into @duplicate
(col1,col2)
values
(2,'two')

insert into @duplicate
(col1,col2)
values
(3,'three')


select col2
from @duplicate
group by col2
having COUNT(col2) > 1 -- could be count(*)

go

You should have gotten 2 rows back one for the word "one" and one for the word "two". If you change the having clause in the query to be > 2 then you'll only get one row back. This wil be the word "one" for (hopefully) obviously reasons based on the inserts.

However, the having clause can be put to a much more useful purpose when producing a query that contains aggregates. let us say that you were asked to generate a report that returned the sums of a column? No big deal right?

Select sum(column), group_by_column
from table
group by group_by_column

What if you were asked to allow an input variable to the query that allowed you to filter on the sum of the "sum(column)"?

Enter the having clause. Think of it as a where clause for aggregates. Here is a simplified example:

declare @t table
(
col1 int,
col2 int
)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(2,1)

insert into @t
(col1,col2)
values
(2,1)

-- throws aggregate error
select col1,SUM(col2)
from @t
where SUM(col2) > 2
group by col1


This code used an aggregate in the where clause and should have cause the following error:

Msg 147, Level 15, State 1, Line 36
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Now use the having clause instead

-- having!
select col1,SUM(col2)
from @t
where 1 = 1 -- used just to show where the "where" clause would go if needed.
group by col1
having SUM(col2) > 2

Coming full circle you could declare a variable and apply this variable to your having clause such as having sum(col2) > @variable.

Put the having clause to work for you when needing to identify duplicates or filter on aggregates.

Happy coding!