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)
Assignment:
1.List departments and average ages where the average age in more than 30.
Possible Answers:
1.mysql> select title, AVG(age)
-> from employee_data
-> GROUP BY title
-> HAVING AVG(age) > 30;
+—————————-+———-+
| title | AVG(age) |
+—————————-+———-+
| Customer Service Manager | 32.0000 |
| Finance Manager | 36.0000 |
| Multimedia Programmer | 36.0000 |
| Programmer | 30.5000 |
| Senior Marketing Executive | 36.0000 |
| Senior Programmer | 32.0000 |
| Senior Web Designer | 32.0000 |
| System Administrator | 34.5000 |
+—————————-+———-+
8 rows in set (0.00 sec)