HAVING Clause

Introduction

The HAVING clause is used in SQL to filter groups of data after they have been aggregated. Unlike the WHERE clause, which filters individual rows, the HAVING clause works with aggregated data using functions like COUNT(), SUM(), AVG(), MAX(), or MIN().

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Key Points:

• GROUP BY is required to group rows before filtering.
• The HAVING clause comes after the GROUP BY clause.
• Aggregate functions are used inside the HAVING condition.

Example 1: Basic HAVING Clause

Suppose you have a table named Orders:

CustomerID	OrderAmount
1	500
2	200
1	300
3	1000
2	800

You want to find customers whose total orders exceed 700.

SELECT CustomerID, SUM(OrderAmount) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > 700;

Result:

CustomerID	TotalOrders
1	800
2	1000
3	1000

Example 2: Using COUNT() with HAVING

Find products that have been ordered more than 5 times from the OrderDetails table.

SELECT ProductID, COUNT(*) AS NumberOfOrders
FROM OrderDetails
GROUP BY ProductID
HAVING COUNT(*) > 5;

Important Notes

• Use WHERE for filtering rows before aggregation.
• Use HAVING for filtering after aggregation.
• You can combine HAVING with WHERE for more precise results.

Summary

The HAVING clause is a powerful tool for filtering grouped data based on aggregate values. Always remember: WHERE filters rows, HAVING filters groups.