Select Groups of Data: the having Clause

Use the having clause to display or reject rows defined by the group by clause.

The having clause sets conditions for the group by clause in the same way where sets conditions for the select clause, except where cannot include aggregates, while having often does. This example is allowed:

select title_id 
from titles 
where title_id like "PS%" 
having avg(price) > $2.0

But this example is not:

select title_id
from titles 
where avg(price) > $20
--------------------
Msg 147, Level 15, State 1
Line 1:
An aggregate function may not appear in a WHERE clause unless it is in a subquery that is in a HAVING clause, and the column being aggregated is in a table named in a FROM clause outside of the subquery.

having clauses can reference any of the items that appear in the select list.

This statement is an example of a having clause with an aggregate function. It groups the rows in the titles table by type, but eliminates the groups that include only one book:

select type 
from titles 
group by type 
having count(*) > 1 
type 
---------------- 
business 
mod_cook 
popular_comp 
psychology 
trad_cook 
 
(5 rows affected)

Here is an example of a having clause without aggregates. It groups the titles table by type and returns only those types that start with the letter “p”:

select type 
from titles 
group by type 
having type like "p%" 
type 
------------ 
popular_comp 
psychology 
 
(2 rows affected) 

When you include more than one condition in the having clause, combine the conditions with and, or, or not. For example, to group the titles table by publisher, and to include only those publishers who have paid more than $15,000 in total advances, whose books average less than $18 in price, and for which the book identification numbers (pub_id) are greater than 0800, the statement is:

select pub_id, sum(advance), avg(price)
from titles
group by pub_id
having sum(advance) > 15000
  and avg(price) < 18
  and pub_id > "0800"
pub_id 
------  ---------------- ---------------- 
0877          41,000.00            15.41 
 
(1 row affected)