MySQL HAVING Clause

The HAVING clause is used to filter the results of a GROUP BY query. It is similar to the WHERE clause, but WHERE cannot be used with aggregate functions (like COUNT(), SUM(), etc.). The HAVING clause is applied after the GROUP BY operation, making it suitable for filtering results based on aggregated data.

HAVING Syntax

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

• WHERE condition: Filters the rows before grouping (optional).
• GROUP BY column_name(s): Groups the rows by specified column(s).
• HAVING condition: Filters the grouped rows based on the aggregate condition.
• ORDER BY column_name(s): Orders the result by specified column(s) (optional).

MySQL HAVING Examples

1. Example 1: Number of customers in each country (Only countries with more than 5 customers)

This query lists countries with more than 5 customers:

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

• The HAVING clause filters the grouped results, showing only countries with more than 5 customers.

2. Example 2: Number of customers in each country, sorted high to low (Only countries with more than 5 customers)

This query counts the number of customers per country and orders them in descending order, but only includes countries with more than 5 customers:

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

• The HAVING clause filters the countries with more than 5 customers, while the ORDER BY clause sorts the results in descending order of customer count.

3. Example 3: Employees who have registered more than 10 orders

This query lists employees who have registered more than 10 orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

• The query groups the results by employee last name and filters the results to include only employees who have more than 10 orders.

4. Example 4: Checking if employees "Davolio" or "Fuller" have registered more than 25 orders

This query lists if specific employees have registered more than 25 orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

• The WHERE clause filters employees by last name, and the HAVING clause ensures that only employees with more than 25 orders are included.

Key Points about HAVING

• HAVING is used to filter grouped data, whereas WHERE is used to filter data before it is grouped.
• You cannot use aggregate functions (e.g., COUNT(), SUM(), AVG()) in a WHERE clause, but you can use them in a HAVING clause.
• HAVING can be used with GROUP BY to filter the aggregated results based on conditions applied to the grouped data.

The HAVING clause is essential when you need to filter the results of aggregation and summarization.