MySQL HAVING Keyword
The MySQL HAVING keyword is used to specify conditions with aggregate functions. Note that the MySQL WHERE keyword can not be used to specify conditions with aggregate functions.
Syntax
The syntax for using HAVING keyword in MySQL is given below:
SELECT column_name(s) FROM table_name WHERE condition(s) GROUP BY column_name(s) HAVING condition(s) ORDER BY column_name(s);
Please note that the HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.
Example:
Consider a database containing tables called Employee and Bonus_Paid with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | London | 24 | 2750 |
3 | Jo | London | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Amsterdam | 28 | 2800 |
Table 2: Bonus_Paid table
EmpID | Bonus |
---|---|
1 | 500 |
2 | 400 |
3 | 450 |
4 | 550 |
5 | 400 |
6 | 600 |
-
To fetch a list containing number of employees (from Employee table) in each city and only including cities with more than one employees, the query is given below:
SELECT COUNT(EmpID) AS Number_of_Employee, City FROM Employee GROUP BY City HAVING COUNT(EmpID) > 1;
This will produce the result as shown below:
Number_of_Employee City 2 Amsterdam 3 London To get above table sorted in descending order based on number of employees, the query will be:
SELECT COUNT(EmpID), City FROM Employee GROUP BY City HAVING COUNT(EmpID) > 1 ORDER BY COUNT(EmpID) DESC;
This result of the query will be:
Number_of_Employee City 3 London 2 Amsterdam -
Using HAVING keyword with JOINs: To fetch a list containing bonus paid to employees in each city and only including cities with more than one employees, the query is given below:
SELECT A.City, SUM(B.Bonus) AS BonusAmount FROM Employee AS A INNER JOIN Bonus_Paid AS B ON A.EmpID = B.EmpID GROUP BY City HAVING COUNT(A.EmpID) > 1;
This result of the following code will be:
City BonusAmount Amsterdam 1150 London 1350
❮ MySQL Keywords