HAVING

To select groups of interest to us is the HAVING clause. HAVING clause is placed in a condition expressed by using the group.

To select the average salary for professional groups, where the maximum income is higher than 2000, type:

SELECT JOB, AVG (SAL)
FROM EMP GROUP BY JOB HAVING MAX (SAL)> 2000;

The HAVING clause can precede the GROUP BY clause or vice versa.

Sometimes the same criterion can be expressed either using a HAVING clause and the WHERE clause.

SELECT JOB, AVG (SAL) FROM EMP HAVING JOB <> 'CLERK' GROUP BY JOB;

or

SELECT JOB, AVG (SAL) FROM EMP WHERE JOB <> 'CLERK' GROUP BY JOB;

In this situation, the more effective is to put a condition in the WHERE clause.