MySQL Tutorial – Using HAVING clause
Using HAVING clause
HAVING clause is somewhat similar to WHERE clause and it is used together with the GROUP BY clause. It was added to SQL to combine with the aggregate functions because the WHERE clause doesn’t allow aggregates.
The syntax of HAVING clause:
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING aggregate_function(column_name) operator value;
Let’s see some example:
database table: (employee_record)
| id | f_name | l_name | position | age | salary | |
|---|---|---|---|---|---|---|
| 1 | Piolo | Pascual | Programmer | 38 | 25000 | piolopascual@yahoo.com |
| 2 | Sam | Milby | System Analyst | 34 | 20000 | sammilby@yahoo.com |
| 3 | John Lloyd | Cruz | Network Administrator | 33 | 20000 | johnllyodcruz@yahoo.com |
| 4 | Rolan | Algara | Encoder | 19 | 55000 | cuteness_tyron@yahoo.com |
| 5 | Jericho | Rosales | Encoder | 25 | 55000 | jerichorosales@yahoo.com |
| 6 | John | Prats | Programmer | 24 | 15000 | johnprats@yahoo.com |
We want to list the position of the employee that is having a salary greater then 20000. Issue the following command:
SELECT position, MAX(salary) FROM employee_record GROUP BY position HAVING MAX(salary) > 20000;
Result:
+------------+-------------+ | position | MAX(salary) | +------------+-------------+ | Encoder | 55000 | | Programmer | 25000 | +------------+-------------+ 2 rows in set (0.00 sec)
