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;