In this tutorial, we will learn about the MySQL
HAVING Clause. The MySQL
HAVING clause is used along with the
GROUP BY clause. The HAVING Clause adds a condition to the groups and returns only those groups for whom the condition evaluates to true. In short, it restricts the rows returned by the
GROUP BY clause.
Syntax of MySQL HAVING
SELECT column_name(s) FROM table_name [WHERE condition] GROUP BY column_name(s) HAVING condition(s);
WHERE condition is optional.
The Need for MySQL HAVING
Consider the below Employee table.
Now, you would wonder why would we need another clause for checking conditions when we already have the
WHERE clause? Let’s take an example.
Suppose you want to find out the department-wise sum of the salaries of all employees. You want to display only those departments which have their corresponding salary total above 100000. So, you’d normally use a query like this,
SELECT Department, SUM(Salary) FROM Employee WHERE SUM(Salary)>100000 GROUP BY Department;
And you get the output as,
Wait a minute. What was that? The problem with the WHERE Clause is that we cannot use it with aggregate functions. Using WHERE clause and having an aggregate function in the condition gives us an error.
Examples of MySQL HAVING
With the basics out of the way, let’s get right to understand what the MySQL HAVING clause is all about.
Simple Example of MySQL HAVING
Building on the error we got earlier, it is quite clear that we can’t use aggregate functions as conditions in the
WHERE clause. So let us see how the earlier question can be solved. The query that we should use for it is,
SELECT Department, SUM(Salary) FROM Employee GROUP BY Department HAVING SUM(Salary)>100000;
And we get the output as,
MySQL HAVING With the WHERE Clause
Now let us see in which situations we can use the
WHERE clause. Let’s take the same question as above and add a little condition to it. So now, you want to find out the department-wise sum of the salaries of all employees, except the ones from the ‘Executive’ department.
You want to display only those departments which have their corresponding salary total above 100,000. This is where we find the use of the
SELECT Department, SUM(Salary) FROM Employee WHERE Department!='Executive' GROUP BY Department HAVING SUM(Salary)>100000;
And our output is as follows:
MySQL HAVING With Multiple Conditions
The above question can be solved in another way. Instead of using the
WHERE clause, we will make use of the
AND operator in the condition specified by the
HAVING clause. This is what our query looks like:
SELECT Department, SUM(Salary) FROM Employee GROUP BY Department HAVING SUM(Salary)>100000 AND Department!='Executive';
We get out output as,
Preferably, use a WHERE clause for such conditions and stick to using the HAVING clause only for the conditions that involve aggregate functions.
MySQL HAVING with COUNT()
Let us count the number of employees for each Office_Code and display the Office_Code and the corresponding employee count only if the Office_Code has more than 2 employees. We will use the following query with
SELECT Office_Code, COUNT(*) FROM Employee GROUP BY Office_Code HAVING COUNT(*)>2;
And we get the output as:
Restricting the output we get from
GROUP BY can be very useful as it acts as an added layer of filter in our querying. The pairing of the
HAVING clause with
GROUP BY proves very important in data analysis in large tables.
- MySQL official documentation on the
HAVINGclause under the