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)
