GROUP BY clause was discussed in the previous topic that allows you to summarize data with aggregate functions a SELECT statement. You can filter the data before grouping by using WHERE clause.
What if you need to filter the grouped data? For this you can use MySQL HAVING clause after the GROUP BY clause. HAVING clause applies the condition on the summarized data.
Consider these examples where MySQL HAVING clause will be useful.
- Find the list of departments whose total salary is greater than $100000.
- Find the total salaries of departments whose average salary is greater than $2000.
You can see that in all these cases we require data filtration after the summarization is done. In the first query given above you will find the total salaries of all departments and display only those departments whose total salary exceeds the given limit.
Difference between MySQL HAVING and WHERE clauses
WHERE clause applies the condition to choose only those rows which satisfy the condition. HAVING clause applies the condition on the summarized data of the aggregate function(s).
WHERE condition- Before summarization
HAVING condition- After Summarization (applied on output of aggregate function in HAVING clause)
Syntax of MySQL HAVING
MySQL HAVING clause is used in a SELECT statement only when GROUP BY clause is used. It is added after GROUP BY clause when you need to filter records on the basis of aggregated values.
SELECT [All| DISTINCT ] Column Name1 AS alias,Column Name2,…, [SUM|AVG|MIN|MAX|COUNT(Column Name11,)…]FROM table name [WHERE condition expression] [GROUP BY Column Name1, Column Name2,… ] [HAVING condition_on_aggregate]
- SELECT, All, DISTINCT, FROM and WHERE are the keywords used in MySQL SELECT statement. ALL keyword will display duplicate records and DISTINCT keyword will display only distinct rows if duplicate rows are stored in the table.
- Column Name1, Column Name2,… – are the columns on which you want to group the data give as the output of SELECT statement. Columns names can be given in any order. Data will be presented in the sequence of columns specified in the MySQL Select statement overriding the sequence of columns in a table. AS keyword followed by an alias is used to give a different heading name to output column.
- table name is the name of the table to access data from.
- SUM|AVG|MIN|MAX|COUNT are the MySQL aggregate functions.
- GROUP BY clause is followed by columns names listed in SELECT part of query other than those used in the Aggregate functions.
- Condition expression in the WHERE filters rows before aggregate function executes. The condition expression in the HAVING display data that matches the condition on aggregated values .
Examples of MySQL HAVING
Find Department-wise Total Salaries
The following query displays total salaries for all the departments whose total salary is less than 50000.(compare this with query result of GROUP BY clause of last topic)
SELECT department_id , SUM(salary) FROM employees GROUP BY department_id having SUM(salary)<50000;
Find Job wise total salaries
This query displays job_id wise average salaries in the company when average salaries are between 10000 and 20000 .
SELECT job_id , AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary) BETWEEN 10000 AND 20000;
Using WHERE clause to filter data rows
Salaries greater than 5000 are added with grouping of job ids and from the rows obtained only those are displayed where job id wise total salaries are greater than 20000.
SELECT job_id SUM(salary) FROM employees WHERE SALARY>5000 GROUP BY job_id HAVING SUM(salary)>20000;
Different aggregate functions in SELECT and HAVING clauses
In the query average of salaries greater than 5000 are calculated with grouping of job ids and from these only those are displayed where job id wise total salaries are greater than 20000. (AVG used for displaying data and SUM used in HAVING clause)
SELECT job_id , AVG(salary) FROM employees WHERE SALARY>5000 GROUP BY job_id HAVING SUM(salary)>20000;
Points to remember
- HAVING clause condition uses only aggregate functions for filtering rows.
- WHERE clause can be used along with HAVING clause in a query.
- Aggregate functions in SELECT clause to display data and HAVING clause to choose rows can be different.