In this lesson, you will learn how to apply conditional constraints to summarized data using the HAVING clause.
The Problem with WHERE
So far, you’ve used the WHERE clause to filter individual rows before they are grouped. However, there are times when you need to filter the results after those rows have been aggregated.
Imagine you have a table of Orders. If you group them by CustomerID to calculate the COUNT of orders per person, you might want to see only those customers who have placed more than five orders. You cannot use WHERE for this because WHERE looks at the raw rows in the table, not the result of the COUNT function.
Enter HAVING
The HAVING clause acts as a filter that specifically looks at the aggregated groups created by GROUP BY. Think of it as the gatekeeper that sits at the very end of the query process, only allowing groups that meet your criteria to appear in your final results.
Here is what that looks like in practice:
SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;
The Order of Operations
It is crucial to understand the "lifetime" of a query. SQL processes your instructions in a specific order:
FROM: Identifies the source data.WHERE: Filters the individual rows.GROUP BY: Collapses rows into groups.HAVING: Filters those resulting groups.SELECT: Displays the final columns.
Because HAVING happens after GROUP BY, it is the only place where you can use aggregate functions like SUM(), AVG(), or COUNT() to limit your output. If you try to put an aggregate function inside a WHERE clause, the database will return an error because it hasn't performed the grouping yet.
By using HAVING, you move beyond simple row-level filtering and start asking high-level questions about patterns in your data, such as "Which product categories have an average price over 10,000?"
Key Takeaway: Use WHERE to filter raw rows before grouping, and use HAVING to filter the results after grouping has been performed.