A query with a having clause should also have a group by clause. If you omit group by, all the rows not excluded by the where clause return as a single group.
Because no grouping is performed between the where and having clauses, they cannot act independently of each other. having acts like where because it affects the rows in a single group rather than groups, except the having clause can still use aggregates.
This example uses the having clause averages the price, excludes from the results titles with advances greater than $4,000, and produces results where price is less than the average price:
select title_id, advance, price from titles where advance < 4000 having price > avg(price)
title_id advance price ------------- --------- -------- BU1032 5,000.00 19.99 BU7832 5,000.00 19.99 MC2222 0.00 19.99 PC1035 7,000.00 22.95 PC8888 8,000.00 20.00 PS1372 7,000.00 21.59 PS3333 2,000.00 19.99 TC3218 7,000.00 20.95 (8 rows affected)
You can also use the having clause with the Transact-SQL extension that allows you to omit the group by clause from a query that includes an aggregate in its select list. These scalar aggregate functions calculate values for the table as a single group, not for groups within the table.
In this example, the group by clause is omitted, which makes the aggregate function calculate a value for the entire table. The having clause excludes non-matching rows from the result group.
select pub_id, count(pub_id) from publishers having pub_id < "1000"
pub_id ------ ---------------- 0736 3 0877 3 (2 rows affected)
