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;
CustomerIDCustomerNameAgeCityCountry
1Christina40LondonUnited Kingdom
2Maria Anders56BerlinGermany
3Matthew34LuleåSweden
4Alen51BerlinGermany
5Jacob28México D.FMexico
6Aastha26ChandigarhIndia
7Joshua21México D.FMexico

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;
Number of customersCountry
2Germany
2Mexico

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

SELECT * FROM orders;
OrderIDCustomerIDOrderDate
122017-02-02
222017-03-23
342017-06-14
452017-06-17
562017-07-28
662017-07-31
772017-09-10
862017-09-12

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;
CustomerNameTotalOrder
Aastha3

Leave A Reply

Your email address will not be published.