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)