In SQL, the HAVING clause is used to filter records after performing aggregation with GROUP BY. While the WHERE clause filters rows before grouping, the HAVING clause allows us to filter the results of aggregate functions like COUNT(), SUM(), AVG(), and more.
In this blog post, we’ll explain the HAVING clause with simple examples and practical use cases. By the end, you’ll understand how to use HAVING to refine your SQL queries effectively.
Note: The HAVING clause is specifically designed for working with aggregate functions and is often used in combination with GROUP BY.
Let’s consider the following table with Indian customer data, showing their respective cities:
| CustomerID | CustomerName | City |
|---|---|---|
| 1 | Amit Kumar | Mumbai |
| 2 | Priya Sharma | Delhi |
| 3 | Ravi Singh | Bangalore |
| 4 | Neha Gupta | Chennai |
| 5 | Sandeep Verma | Delhi |
| 6 | Shalini Reddy | Bangalore |
| 7 | Ravi Kumar | Delhi |
This table shows customer data with their respective cities. If you want to count how many customers belong to each city, and only include cities with more than 1 customer, you could use the HAVING clause.
Here’s a query that counts the number of customers per city, only including cities with more than 1 customer:
SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) > 1;
In this query:
If you want to sort the cities based on the number of customers in descending order, you can add the ORDER BY clause:
SELECT COUNT(CustomerID), City
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) > 1
ORDER BY COUNT(CustomerID) DESC;
This query sorts the cities where the number of customers is greater than 1, from the highest to the lowest count.
Now let’s look at another example. We want to find employees who have processed more than 10 orders:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName
HAVING COUNT(Orders.OrderID) > 10;
This query joins the Orders table with the Employees table, groups the results by employee last name, and filters out employees with fewer than 10 orders.
Finally, let’s check whether the employees “Davolio” or “Fuller” have processed more than 25 orders:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.LastName = 'Davolio' OR Employees.LastName = 'Fuller'
GROUP BY Employees.LastName
HAVING COUNT(Orders.OrderID) > 25;
This query filters the results to only include employees “Davolio” and “Fuller,” showing how many orders they have processed if they exceed 25 orders.