HAVING clause

 

The HAVING clause specifies a search condition for the groups selected by the GROUP BY clause.

The HAVING clause says that you want only those groups that satisfy the condition in that clause. Therefore, the search condition you specify in the HAVING clause must test properties of each group rather than properties of individual rows in the group.

The HAVING clause follows the GROUP BY clause and can contain the same kind of search condition as you can specify in a WHERE clause. In addition, you can specify aggregate functions in a HAVING clause. For example, suppose that you want to retrieve the average salary of women in each department. To do this, use the AVG aggregate function and group the resulting rows by WORKDEPT and specify a WHERE clause of SEX = 'F'.

To specify that you want this data only when all the female employees in the selected department have an education level equal to or greater than 16 (a college graduate), use the HAVING clause. The HAVING clause tests a property of the group. In this case, the test is on MIN(EDLEVEL), which is a group property:

SELECT WORKDEPT, DECIMAL(AVG(SALARY),5,0) AS AVG_WAGES, MIN(EDLEVEL) AS MIN_EDUC        FROM CORPDATA.EMPLOYEE        WHERE SEX='F'
       GROUP BY WORKDEPT        HAVING MIN(EDLEVEL)>=16

The result follows.

WORKDEPT	AVG_WAGES	MIN_EDUC
A00	49625	18
C01	29722	16
D11	25817	17

You can use multiple predicates in a HAVING clause by connecting them with AND and OR, and you can use NOT for any predicate of a search condition.

If you intend to update a column or delete a row, you cannot include a GROUP BY or HAVING clause in the SELECT statement within a DECLARE CURSOR statement. These clauses make it a read-only cursor.

Predicates with arguments that are not aggregate functions can be coded in either WHERE or HAVING clauses. It is typically more efficient to code the selection criteria in the WHERE clause because it is handled earlier in the query processing. The HAVING selection is performed in post processing of the result table.

If the search condition contains predicates involving character, or UCS-2 or UTF-16 graphic columns, the sort sequence in effect when the query is run is applied to those predicates.

 

Parent topic:

Retrieving data using the SELECT statement

 

Related concepts

Sort sequences and normalization in SQL

 

Related reference

Using a cursor