HAVING clause

To list the average salary of employees in different departments (titles), we use the GROUP BY clause, as in:

select title, AVG(salary)
from employee_data
GROUP BY title;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Customer Service Manager   |  70000.0000 |
| Finance Manager            | 120000.0000 |
| Marketing Executive        |  77333.3333 |
| Multimedia Programmer      |  83333.3333 |
| Programmer                 |  75000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
| System Administrator       |  95000.0000 |
| Web Designer               |  87500.0000 |
+----------------------------+-------------+
11 rows in set (0.00 sec)

Now, suppose you want to list only the departments where the average salary is more than $100000, you can’t do it, even if you assign a pseudo name to AVG(salary) column. Here, the HAVING clause comes to our rescue.

select title, AVG(salary)
from employee_data
GROUP BY title
HAVING AVG(salary) > 100000;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Finance Manager            | 120000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
+----------------------------+-------------+
5 rows in set (0.00 sec)