MySQL HAVING Clause

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 HAVING clause is used in combination with the GROUP BY clause to filter groups based on a specified condition. It always returns the rows where condition is TRUE.

HAVING Clause Syntax

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

To understand MySQL HAVING clause , consider an “customers” table, which is having the following records:

SELECT * FROM customers;

Now suppose based on the above table you want to count the number of customers in each country. Only include countries with more than 1 customers, then you can do it as follows:

SELECT COUNT(CustomerID) AS "Number of customers", Country 
FROM customers 
GROUP BY Country
HAVING COUNT(CustomerID) > 1;

Below is a selection from the “orders” table, which is having the following records:

SELECT * FROM orders;

The following MySQL statement lists the customers that have more than 2 orders:

SELECT CustomerName, COUNT(orders.OrderID) AS TotalOrder
FROM customers
INNER JOIN Orders 
ON customers.CustomerID = orders.CustomerID
GROUP BY customers.CustomerID
HAVING TotalOrder > 2;