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
FROMclause of a subquery appearing in aHAVINGclause cannot name any table or view specified in the main query’sFROMclause. - A correlated subquery cannot be used in a
HAVINGclause.
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;
HAVING can also be used without GROUP BY. In this case, all rows retrieved by a SELECT are treated as a single group, and each column named in the SELECT 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.