Having Clause in MySQL

The MySQL HAVING Clause, utilized alongside the GROUP BY clause, filters grouped rows based on a specified condition, exclusively returning rows where the condition is TRUE. Acting on groups formed by GROUP BY, the HAVING clause allows the application of conditions, evaluating each group, and including those meeting the criteria. Unlike the WHERE clause, which operates on individual rows, HAVING functions on groups. When GROUP BY is omitted, HAVING behaves akin to WHERE. MySQL processes the HAVING clause after FROM, WHERE, SELECT, and GROUP BY, preceding DISTINCT, SELECT, ORDER BY, and LIMIT clauses, as per SQL standard specifications.

Syntax

The following is the syntax of the HAVING clause in MySQL:

Why Do We Need Having Clause in MySQL?

The Having clause in MySQL is primarily used for filtering the data that is based on aggregate functions. The having clause in MySQL is used in conjunction with the GROUP BY clause to filter groups that meet the specified conditions.

Suppose we are given in a table of employees with their roles and salaries. Now suppose we are asked to filter employees who have a salary of more than 10000. This can easily be done by using the WHERE clause by executing the following query:

Now suppose we encounter a case where we need to get all roles from the employees table whose count is greater than 100. In this case, the above query will fail as there is no way to filter the results for each group using the WHERE clause.

Then how do we add the filter for grouping employees in this case? Thus for the cases where we have to filter grouped data on certain conditions, we use the HAVING clause in MySQL.

How to use Having Clause in MySQL?

In this section, we will learn how to use the HAVING clause in MySQL. In the problem discussed in the above section, first of all, we will group the employees based on their roles using the following query:

Now we will have a table will all the data grouped by roles, now to display the roles having a count greater than 100 we will use the HAVING clause in MySQL to display the filtered data in the following way:

Here in the above query, the COUNT(*) will count the number of employees in each role, then the HAVING count > 100 will filter the output with those roles that have an employee count of more than 100.

Using Both Having and Where Clause in MySQL

The WHERE clause in MySQL filters data based on rows when provided with one or more than one condition. The WHERE clause is always used before the GROUP BY clause. Thus it is necessary to use the HAVING clause in MySQL after the WHERE clause. Thus the having clause in MySQL is used to filter the result set based on the aggregate method's response after the grouping of the data is done.

Following is an example of how to use the Having and Where clauses together in MySQL:

Suppose we are given a Customers table and we want the data of all countries other than the USA whose average age is greater than 25.

The following query will be applied to find out data from all countries other than the USA whose average age is greater than 25:

Code:

Output:

• Here the WHERE COUNTRY != "USA" will filter all the countries that are not the USA
• The GROUP BY country will group the countries by country name.
• The HAVING avg_age>25 will filter all countries with an average age of more than 25.

Having Clause Using COUNT, SUM, MIN, MAX, and AVG Aggregate Functions

In this section, we will go through some examples to learn how to work with aggregate functions using clauses in MySQL. For this section, Suppose we have a table called sales with the following columns:

• id: unique identifier for each sale
• customer_id: an identifier for the customer who made the purchase
• product_name: name of the product that was sold
• Price: the price of the product in dollars
• quantity: quantity of the product sold

The table will look like the following:

Example 1: Using having clause with COUNT aggregate function

In this example, we will learn how to use the HAVING clause along with the COUNT aggregate function. Suppose we have to find out data grouped by customer_id where the sales number is greater than 2. In this case, we can use the COUNT aggregate function.

Code:

Output:

Explanation of the example: In the above example, we were supposed to find out all the customer_id where the total no. of sales is more than 2. Thus to group the data by customer_id we have used GROUP BY customer_id. Now the COUNT(*) as total_sales will count the total sales for each customer_id. The HAVING COUNT(*) > 2 will filter the data for customer_id having 'total_sales > 2' thus we will get our desired result.

Example 2: Using having clause with SUM aggregate function

In this example, we will learn how to use the HAVING clause along with the SUM aggregate function. Suppose we have to find out data grouped by customer_id where the revenue is greater than 100. In this case, we can use the SUM aggregate function.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to find out all the customer_id where the total revenue is more than 100. This can be calculated by adding the product of price and quantity sold for each customer_id. Thus to group the data by customer_id we have used GROUP BY customer_id. Now the SUM(price * quantity) will sum the total revenue for each customer_id. The HAVING SUM(price * quantity) > 100 will filter the data for customer_id having 'total_revenue > 100' thus we will get our desired result.

Example 3: Using having clause with MIN aggregate function

In this example, we will learn how to use the HAVING clause along with the MIN aggregate function. Suppose we have found the data of the minimum price of less than 20 among the products grouped by product name. In this case, we can use the MIN aggregate function.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to find out the minimum price of less than 20 among the products grouped by the product name. Thus to group the data by product_name we have used GROUP BY product_name. Now the MIN(price) will fetch the minimum price and the query so far will return the product_name and the minimum price of each product. The MIN(price) < 20 will filter the data for product_name having 'min_price < 20' thus we will get our desired result.

Example 4: Using having clause with MAX Aggregate Function

In this example, we will learn how to use the HAVING clause along with the MAX aggregate function. Suppose we have to find the data of the maximum price greater than 50 among the products grouped by product name. In this case, we can use the MAX aggregate function.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to find out the maximum price greater than 50 among the products grouped by product name. Thus to group the data by product_name we have used GROUP BY product_name. Now the MAX(price) will fetch the maximum price and the query so far will return the product_name and the maximum price of each product. The HAVING MAX(price) > 50 will filter the data for product_name having 'max_price > 50' thus we will get our desired result.

Example 5: Using having clause with AVG aggregate function

In this example, we will learn how to use the HAVING clause along with AVG aggregate function. Suppose we have to calculate the AVG sale among each customer_id where the average_salary is more than 50. In this case, we can use the AVG aggregate function.

Code:

Output:

Explanation of the example:

In the above example, we were asked to display the average sale among each customer_id where the average_sale is more than 50. Thus, the first step will be to group the members based on their roles using GROUP BY customer_id. Now, this query will return the customer_id and the average sale made by each customer. Thus we will use the HAVING AVG(price * quantity) > 50 which will filter data for customer_id whose avg sale is more than 50.

Difference Between Where Clause and Having Clause in MySQL

In this section we will go through the differences between the WHERE clause and HAVING clause in MySQL: