WebDevelopersNotes logo

home-icon Home / MySQL / MySQL development tutorial – HAVING clause

MySQL development tutorial – HAVING clause

MySQL development tutorial - 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)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

The most used keyboard shortcuts - Cut, Copy, Paste, Undo - were popularised by Apple products, the Lisa and the Macintosh. Apple came up with the combinations - Command key with an alphabet key - to achieve these features. [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy