MySQL – HAVING Clause

By using the MySQL HAVING clause in conjunction with the GROUP BY clause, you are able to restrict the returned rows to only those for which the condition is true.

Syntax

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

aggregate_function – This can be a function such as SUM, COUNT, MIN, MAX, or AVG.

expression1, expression2, … expression_n – In the GROUP BY clause, you should include expressions that are not encapsulated in an aggregate function.

WHERE conditions – This is optional. The following are the conditions under which records will be selected.

HAVING condition – It is a further condition applied only to the aggregated results to restrict the groups of rows to be returned. The result set only includes groups whose condition evaluates to TRUE.

Example – Using SUM function

You could also use the SUM function to return the name of the role and the total salary (for that role). The MySQL HAVING clause will filter the results so that only last_name with a total salary greater than 15000 will be returned.

SELECT last_name, SUM(salary) AS "Total Salary"
FROM employees
GROUP BY last_name
HAVING SUM(salary) > 15000;

Example – Using COUNT function

You could use the COUNT function to return the name of the last_name and the number of employees (for thatlast_name) that are in the Sluis category. The MySQL HAVING clause will filter the results so that only products with less than 10000 salaries will be returned.

SELECT last_name, COUNT(last_name) AS "Number of employees"
FROM employees
WHERE last_name = 'Sluis'
GROUP BY last_name
HAVING COUNT(last_name) < 10000;

Example – Using MIN function

You could also use the MIN function to return the name of each last_name and the minimum salary in the last_name.

SELECT last_name, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY last_name
HAVING MIN(salary) < 20000;

Example – Using MAX function

You could also use the MAX function to return the name of each last_name and the maximum salary in thelast_name.

SELECT last_name, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY last_name
HAVING MAX(salary) > 20000;