HAVING Clause

The HAVING clause is used to filter groups of a grouped table by a search condition.

<having clause> ::= HAVING <search condition>.
         

The <search condition> is applied to all groups of the grouped table. The result is a grouped table consisting of those groups, for which the <search condition> yields true.

If a HAVING clause is applied to a table that is not already grouped, the table is implicitly grouped. The result of implicitly grouping is grouped table with no grouping columns consisting of exactly one group. The <search condition> is applied to this group.

SELECT manager_name, AVG(salary) AS average 
    FROM employees AS e JOIN managers AS m 
        ON e.manager_id = m.manager_id 
    WHERE manager_name IN ('Paul Smith', 
                           'Mary Jones', 
                           'John Miles') 
    GROUP BY manager_name 
    HAVING AVG(salary) > ?

            

The HAVING Clause. For the three given managers, the average salary of all employees associated with the respective manager is created. Finally, only those managers are taken into account who have employees with an average salary that exceeds a given limit.