Ways to specify search conditions

You can use the HAVING clause in a variety of ways to specify search conditions.

Use HAVING to specify a search condition that each retrieved group must satisfy. The HAVING clause acts like a WHERE clause for groups, and it can contain the same kind of search conditions that you can specify in a WHERE clause. The search condition in the HAVING clause tests properties of each group rather than properties of individual rows in the group.

Begin general-use programming interface information.

Example: Consider this query:

SELECT DEPT, AVG(SALARY) AS AVG_SALARY
  FROM EMP
  GROUP BY DEPT
  HAVING COUNT(*)> 1
  ORDER BY DEPT;

The result table looks like this:

DEPT         AVG_SALARY
====     ==============
A00      42833.33333333
C01      31696.66666666
D11      29943.33333333
E21      23053.33333333

The HAVING COUNT(*)> 1 clause ensures that only departments with more than one member are displayed. (In this case, department B01 is not displayed because it consists of only one employee.)

End general-use programming interface information.

Example: You can use the HAVING clause to retrieve the average salary and minimum education level of employees that were hired after 1990 and who report to departments in which the education level of all employees is greater than or equal to 14. Assuming that you want results only from departments A00 and D11, the following SQL statement tests the group property, MIN(EDL):

SELECT DEPT, AVG(SALARY) AS AVG_SALARY,
  MIN(EDL) AS MIN_EDL
  FROM EMP
  WHERE HIREDATE>= '1990-01-01' AND DEPT IN ('A00', 'D11')
  GROUP BY DEPT
  HAVING MIN(EDL)>= 14;

The result table looks like this:

DEPT         AVG_SALARY     MIN_EDL
====     ==============     =======
A00      29250.00000000          14
D11      29943.33333333          16

When you specify both GROUP BY and HAVING, the HAVING clause must follow the GROUP BY clause in the syntax. A function in a HAVING clause can include multiple occurrences of the DISTINCT clause. You can also connect multiple predicates in a HAVING clause with AND and OR, and you can use NOT for any predicate of a search condition.