MySQL Having Clause

The MySQL Having Clause restricts the number of records or rows returned by the Group By Clause. To use the Having Clause, we have to use Group By. It is because the Having is applied after the Group by.

You can’t use Where to check conditions against aggregated data. For this Having Clause, we have to use the Group By. Please refer to Group By to understand the grouping.

MySQL Having Clause Syntax

The Syntax of a Having Clause can be written as:

SELECT [Column1],...[ColumnN],        
Aggregate_Function(Column_Name)  
FROM [Source] 
WHERE [Conditions] -- Optional 
GROUP BY [Column1],...[ColumnN] 
HAVING [Conditions] -- Condition is on Aggregate Function(Column_Name)
  • Column1…N: Choose the columns from a table(s).
  • Aggregate Functions: Use any of the aggregate functions. COUNT, SUM, AVG, AVG, MIN, MAX, STD, and VARIANCE are the functions that we can use.
  • Group By: Columns that are not part of an Aggregate Function have to place after this Group by.
  • Having: We can provide the Filters or apply Conditions on the Aggregated Data that we got from the Group By.

MySQL Having Clause Single Condition Example

For this Having Clause to filter the aggregated data demonstration, We are going to use the below-shown data.

Customer Table

MySQL Having Sum Clause Example

In this example, we use a single column as the Group column, the Sum function for the aggregate value. Next, we used the Having clause to restrict the result. Here, the Group By statement Groups the customers by Qualification. Next, it checks whether the Sum of Income is greater than 100000.

SELECT Qualification,
       SUM(Income),
       SUM(Sales)
 FROM customer
 GROUP BY Qualification
 HAVING SUM(Income) > 100000;
MySQL HAVING Sum 2

Having clause Avg Function Example

In this example, we used the AVG function. Here, it checks whether the Average Income is greater than or equal to 60000.

SELECT Qualification,
       AVG(Income),
       AVG(Sales)
 FROM customer
 GROUP BY Qualification
 HAVING AVG(Income) >= 60000;
MySQL Group By HAVING Clause 3

MySQL Having Clause Min Example

In this example, we used Min Function in the Having Clause. The below query returns Minimum Income, Maximum Income, Minimum Sales, and maximum Sales in each Qualification whose Minimum income is greater than 50000.

SELECT Qualification,
       MIN(Income),
       MAX(Income),
       MIN(Sales),
       MAX(Sales)
 FROM customer
 GROUP BY Qualification
 HAVING MIN(Income) > 50000;
HAVING Max and Min 4

Having Max Example

This time, we are using Multiple columns in the Group By. Next, we used the Max function and the Min Function inside the Having Clause.

The below having Clause query returns Minimum yearly Income, Maximum Income, Min Sales, and maximum Sales in each Qualification whose Minimum income is higher than 50000.

SELECT Qualification,
       Occupation,
       MIN(Income),
       MAX(Income)
       MIN(Sales),
       MAX(Sales)
 FROM customer
 GROUP BY Qualification, Occupation
 HAVING MAX(Sales) > 100;
Having Max 5

Having Multiple Conditions

In this example, we are going to use Multiple Conditions in the Having Clause. First Group By statement Groups the customers by Qualification and Occupation. Next, it checks whether the sum of Sales is greater than 100 and the sum of sales is less than 5000.

SELECT Qualification,
       Occupation,
       SUM(Income),
       MIN(Income),
       MAX(Income),
       SUM(Sales),
       MIN(Sales),
       MAX(Sales)
 FROM customer
 GROUP BY Qualification, Occupation
 HAVING SUM(Sales) > 100 AND SUM(Sales) < 5000
 ORDER BY SUM(Income);
HAVING Clause Multiple conditions 6

MySQL Having Order By Clause

We can also use Order By along with the Having Clause. Here, the result is sorted by the Total Income in ascending order.

SELECT Qualification,
       Occupation,
       SUM(Income),
       MIN(Income),
       MAX(Income),
       SUM(Sales),
       MIN(Sales),
       MAX(Sales)
 FROM customer
 GROUP BY Qualification, Occupation
 HAVING SUM(Sales) > 100 AND SUM(Sales) < 5000
 ORDER BY SUM(Income);
Order By Example 7

Command Prompt Example

In this Having clause example, We used the command prompt to display the result.

SELECT Qualification, Occupation,
       SUM(Income),
       AVG(Income),
       MIN(Income),
       MAX(Income)
FROM Customer
GROUP BY Qualification, Occupation
HAVING MIN(Sales) > 100;
MySQL HAVING Clause Command Prompt Example 8