The HAVING clause, when used in conjunction with the GROUP BY in a SELECT statement, tells GROUP BY which groups to include in the output. HAVING is to GROUP BY as WHERE is to SELECT. In other words, the WHERE clause places conditions on the selected columns , whereas the HAVING clause places conditions on groups created by the GROUP BY clause. The following is the position of the HAVING clause in a query: SELECT FROM WHERE GROUP BY HAVING ORDER BY The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause: SELECT COLUMN1, COLUMN2 FROM TABLE1, TABLE2 WHERE CONDITIONS GROUP BY COLUMN1, COLUMN2 HAVING CONDITIONS ORDER BY COLUMN1, COLUMN2 In the following example, you select the average pay rate and salary for all cities except GREENWOOD. You group the output by CITY, but only want to display those groups (cities) that have an average salary greater than $20,000. You sort the results by average salary for each city. SELECT CITY, AVG(PAY_RATE), AVG(SALARY) FROM EMP_PAY_TMP WHERE CITY <> 'GREENWOOD' GROUP BY CITY HAVING AVG(SALARY) > 20000 ORDER BY 3; CITY AVG(PAY_RATE) AVG(SALARY) ------------ ------------- ----------- WHITELAND 40000 1 row selected. Why was only one row returned by this query? -
The city GREENWOOD was eliminated from the WHERE clause. -
INDIANAPOLIS was deducted from the output because the average salary was 20000, which is not greater than 20000. |