Restricting Grouped Rows with HAVING
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 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.
FROMclause of a subquery appearing in a
HAVINGclause cannot name any table or view specified in the main query’s
- A correlated subquery cannot be used in a
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;
HAVINGcan also be used without
GROUP BY. In this case, all rows retrieved by a
SELECTare treated as a single group, and each column named in the
SELECTclause is normally operated on by an aggregate function.