MySQL HAVING Clause

While working with MySQL, you'll often find scenarios where you need to filter out grouped results after performing an aggregation. The HAVING clause in MySQL provides this functionality, acting as a filter for grouped results much in the same way the WHERE clause filters individual rows.

What is the HAVING Clause? 

The HAVING clause is used in the SQL SELECT statement to filter result sets based on a condition applied to the aggregate functions. It's typically used with the GROUP BY clause to filter grouped results after aggregation.

Syntax 

Here's the basic syntax for the HAVING clause in MySQL:

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

Demo Database

We will use the following Orders table for our examples:

Examples 

Basic HAVING Query 

To find customers who have spent more than $50 in total:

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING TotalAmount > 50;

This query groups the orders by CustomerID, calculates the total amount spent by each customer, and only includes those groups where the total is greater than $50. 

Advanced Examples HAVING Multiple Conditions 

To find customers who have made more than 2 orders and spent over $50:

SELECT CustomerID, COUNT(OrderID) AS TotalOrders, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING TotalOrders > 2 AND TotalAmount > 50;

HAVING with Different Aggregate Functions 

To find customers whose average order amount is greater than $15:

SELECT CustomerID, AVG(Amount) AS AverageAmount
FROM Orders
GROUP BY CustomerID
HAVING AverageAmount > 15;

Summary 

The HAVING clause is used to filter the results of a GROUP BY clause based on a specified condition.

Unlike the WHERE clause, which filters rows before they're aggregated, the HAVING clause filters results after aggregation.

It's particularly useful when you want to filter grouped data. For example, if you want to list products that have been ordered more than a certain number of times.