Filtering groups

If you group rows in the result table, you can also specify a search condition that each retrieved group must satisfy. The search condition tests properties of each group rather than properties of individual rows in the group.

Procedure

To filter groups:

Use the HAVING clause to specify a search condition.

The HAVING clause acts like a WHERE clause for groups, and it contains the same kind of search conditions that you specify in a WHERE clause.

Example

Example: HAVING clause

The following SQL statement includes a HAVING clause that specifies a search condition for groups of work departments in the employee table:

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

The result table looks similar to the following output:

WORKDEPT   AVG_SALARY
========   ==============
A00        40850.00000000
C01        29722.50000000
D11        25147.27272727
D21        25668.57142857
E11        21020.00000000
E21        24086.66666666

Compare the preceding example with the second example shown in Summarizing group values. The clause, HAVING COUNT(*) > 1, ensures that only departments with more than one member are displayed. In this case, departments B01 and E01 do not display because the HAVING clause tests a property of the group.

Example: HAVING clause used with a GROUP BY clause

Use the HAVING clause to retrieve the average salary and minimum education level of women in each department for which all female employees have an education level greater than or equal to 16. Assuming that you want results from only departments A00 and D11, the following SQL statement tests the group property, MIN(EDLEVEL):

SELECT WORKDEPT, AVG(SALARY) AS AVG_SALARY,
   MIN(EDLEVEL) AS MIN_EDLEVEL
   FROM DSN8C10.EMP
   WHERE SEX = 'F' AND WORKDEPT IN ('A00', 'D11')
   GROUP BY WORKDEPT
   HAVING MIN(EDLEVEL) >= 16;

The result table looks similar to the following output:

WORKDEPT   AVG_SALARY       MIN_EDLEVEL
========   ==============   ===========
A00        49625.00000000   18
D11        25817.50000000   17

When you specify both GROUP BY and HAVING, the HAVING clause must follow the GROUP BY clause. A function in a HAVING clause can include DISTINCT if you have not used DISTINCT anywhere else in the same SELECT statement. You can also connect multiple predicates in a HAVING clause with AND or OR, and you can use NOT for any predicate of a search condition.