Restricting Grouped Rows with HAVING
Go Up to Understanding Data Retrieval with SELECT
Just as a WHERE
clause reduces the number of rows returned by a SELECT
clause, the HAVING
clause can be used to reduce the number of rows returned by a GROUP BY
clause. The syntax of HAVING
is:
HAVING <search_condition>
HAVING
uses search conditions that are like the search conditions that can appear in the WHERE
clause, but with the following restrictions:
- Each search condition usually corresponds to an aggregate function used in the SELECT clause.
- The
FROM
clause of a subquery appearing in aHAVING
clause cannot name any table or view specified in the main query’sFROM
clause. - A correlated subquery cannot be used in a
HAVING
clause.
For example, the following cursor declaration returns the average salary for all employees in each department. The GROUP BY
clause assures that average salaries are calculated and retrieved based on department names. The HAVING
clause restricts retrieval to those groups where the average salary is greater than 60,000, while the ORDER BY
clause arranges retrieved rows alphabetically by department name.
EXEC SQL DECLARE SIXTY_THOU CURSOR FOR SELECT DEPARTMENT, AVG(SALARY) FROM DEPARTMENT D, EMPLOYEE E WHERE D.DEPT_NO = E.DEPT_NO GROUP BY DEPARTMENT HAVING AVG(SALARY) > 60000 ORDER BY DEPARTMENT;
- Note:
HAVING
can also be used withoutGROUP BY
. In this case, all rows retrieved by aSELECT
are treated as a single group, and each column named in theSELECT
clause is normally operated on by an aggregate function.
For more information about search conditions, see Restricting Row Retrieval with WHERE. For more information about subqueries, see Using Subqueries.