MySQL HAVING Clause

               MySQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

HAVING Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

Customers

MySQL HAVING Examples

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

HAVING clause with COUNT() function

The HAVING clause can be used with the COUNT() function to filter groups based on the number of rows they contain.

Example

Following is the query, which would display a record where count of similar country is greater than or equal to 2.

SELECT Country
FROM CUSTOMERS
GROUP BY Country
HAVING COUNT(Country) >= 2

Employees

HAVING clause with MAX() function

We can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

In here, we are trying to retrieve the Designation of the customers whose maximum salary is less than 1,00,000

SELECT Designation, MAX(salary) as max_salary
FROM Employees
GROUP BY Designation
HAVING MAX(salary) < 1,00,000