MySQL HAVING Clause

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);Code language: SQL (Structured Query Language) (sql)

The 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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 WHERE clause.

SELECT Department, SUM(Salary) FROM Employee WHERE Department!='Executive' GROUP BY Department HAVING SUM(Salary)>100000;Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

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 COUNT()

SELECT Office_Code, COUNT(*) FROM Employee GROUP BY Office_Code HAVING COUNT(*)>2;Code language: SQL (Structured Query Language) (sql)

And we get the output as:

Conclusion

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.

References

• MySQL official documentation on the HAVING clause under the SELECT statement.