Summary: In this chapter, we will learn how to use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates.
Introduction to MySQL HAVING clause
The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
The MySQL HAVING clause is often used with the GROUP BY clause because the WHERE keyword could not be used with aggregate functions. MySQL HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.
MySQL HAVING clause examples
Let’s take a look at an example of using MySQL HAVING clause. We will continue with the employees table data in the sample database as shown in the picture below.
In previous chapter we already learned GROUP BY clause so we can take same example like if we want to know how much salary is depositing based on job profile for every month but here will put one more condition that salary transaction amount should be more than 1 lakh.
we can use the MySQL HAVING clause with the aggregate as follows because WHERE keyword could not be used with aggregate functions like sum, count, etc.
1
2
3
4
5
| SELECT jobtitle, SUM(salary)FROM employeesGROUP BY jobtitleHAVING SUM(salary) > 100000; |
Notice that The MySQL HAVING clause is only useful when we use it with the GROUP BY clause to generate the output of the high-level reports.
In this chapter, we have learnt how to use the MySQL HAVING clause with the aggregate functions like sum, count, etc
MySQL GROUP BY MySQL Subquery
MySQL GROUP BY MySQL Subquery
