The HAVING Clause

HAVING sets conditions for the GROUP BY clause similarly to the way that WHERE sets conditions for the SELECT clause. Because the HAVING clause is used to restrict groups, HAVING without GROUP BY is not meaningful. In queries without GROUP BY, SQL Server returns the same results as it would if a WHERE clause were used.

The HAVING search conditions are identical to WHERE search conditions with one exception: WHERE search conditions cannot include aggregate functions, and HAVING search conditions often do. HAVING clauses can reference any of the items that appear in the select list. Up to 128 conditions can be included in a HAVING clause.

The following statement shows a HAVING clause with an aggregate function. It groups the rows in the titles table by type and 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 row(s) affected)

Here's an example of a HAVING clause without aggregate functions. It groups the rows in the titles table by type and eliminates those types that do not start with the letter "p":

SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'

type
------------
popular_comp
psychology

(2 row(s) affected)

When multiple conditions are included in the HAVING clause, they are combined with AND, OR, or NOT. The following example shows how to group the titles table by publisher, including only those publishers with identification numbers greater than 0800, who have paid more than $15,000 in total advances, and who sell books for less than an average of $20:

SELECT pub_id, SUM(advance), AVG(price)

FROM titles

GROUP BY pub_id

HAVING SUM(advance) > $15000

    AND AVG(price) < $20

    AND pub_id > '0800'

The following statement illustrates the use of GROUP BY, HAVING, WHERE and ORDER BY clauses in one SELECT statement. It produces the same groups and summary values as the previous example but does so after eliminating the titles with prices under $5. It also organizes the results by pub_id:

SELECT pub_id, SUM(advance), AVG(price)

FROM titles

WHERE price >= $5

GROUP BY pub_id

HAVING SUM(advance) > $15000

    AND AVG(price) < $20

    AND pub_id > '0800'

ORDER BY pub_id