MySQL Having Clause

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

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

MySQL Having Clause Syntax

The Syntax of a MySQL Having Clause in MySQL 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. MySQL 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 Single Condition Examples

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

MySQL HAVING Clause 1

MySQL Having Sum Example

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

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

MySQL Having Avg Example

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

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

MySQL Having Min Example

In this example, we used Min Function in the Having Clause. 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 `MySQL Tutorial`.customer
GROUP BY Qualification
HAVING MIN(Income) > 50000;
MySQL HAVING Max 4

MySQL Having Max Example

This time, we are using Multiple columns in the Group By clause. Next, we used Max function inside the Having Clause. In this example, we used Min Function in the Having Clause. Below 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 `MySQL Tutorial`.customer
GROUP BY Qualification, Occupation
HAVING MAX(Sales) > 100;
MySQL HAVING Max 5

MySQL Having Multiple Conditions

In this MySQL example, we are going to use Multiple Conditions in the Having Clause. First Group By statement Groups the customers by Qualification and Occupation. Next, Having Clause check 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 `MySQL Tutorial`.customer
GROUP BY Qualification, Occupation
HAVING SUM(Sales) > 100 AND SUM(Sales) < 5000
ORDER BY SUM(Income); 
MySQL HAVING Clause Multiple conditions 6

MySQL Having Order By Clause

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

SELECT Qualification,
      Occupation,
      SUM(Income),
      MIN(Income),
      MAX(Income),
      SUM(Sales),
      MIN(Sales),
      MAX(Sales)
FROM `MySQL Tutorial`.customer
GROUP BY Qualification, Occupation
HAVING SUM(Sales) > 100 AND SUM(Sales) < 5000
ORDER BY SUM(Income); 
MySQL HAVING Clause 7

MySQL Having Clause Example

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

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