HAVING

HAVING

This clause determines which rows of data to include based on the results of a prior GROUP BY clause.

Options

AND
OR
AND indicates two conditions must both be true.
OR indicates either condition must be true.

condition
Specifies a combination of one or more expressions and/or operations that would evaluate to either true or false. See "HAVING Conditions" below.

NOT
Reverses the meaning of an operator or indicates that a condition is not true.

About the HAVING command

The optional HAVING clause selects rows that meet one or more conditions from among the results of the GROUP BY clause. HAVING works the same as a WHERE clause with the following exceptions:

A WHERE clause modifies the intermediate results of a FROM clause; a HAVING clause modifies the intermediate results of a GROUP BY clause.

A HAVING clause can include SELECT functions.

HAVING Conditions:

Examples

To display sales information for only those employees who have made more than one sale to the same customer, add a HAVING clause such as the following to one of the examples shown previously in "GROUP BY Clause." When used in a HAVING clause, SELECT functions compute results based on the values grouped in the specified column. In this HAVING clause, COUNT returns the number of values grouped in the transid column.

SELECT empid, custid FROM transmaster +
WHERE netamount < $100,000 +
GROUP BY empid, custid HAVING COUNT(transid) > 1

Intermediate Result Table-HAVING COUNT(transid) > 1

transid         custid   empid   netamount
4975, 4980, 5000, 5060   101   102   $87,500, $22,500, $40,500, $57,500
4790, 5050, 5070      104   129   $6,400, $56,250, $95,500
5080            100   133   $32,400, $88,000