Module 6 Lesson 4. HAVING Clause
HAVING — Filtering Grouped Results
WHERE filters rows before aggregation. HAVING filters groups after aggregation. Both are essential — and easy to confuse.
The pattern
```sql SELECT grouping column, aggregate function FROM table WHERE row filter GROUP BY grouping column HAVING group filter ORDER BY ... ; ```
Example: top-performing regions
```sql SELECT Region, SUM(SalesAmount) AS Revenue FROM Sales GROUP BY Region HAVING SUM(SalesAmount) greater than 50000 ORDER BY Revenue DESC; ``` You can't put SUM(SalesAmount) greater than 50000 in the WHERE clause — SUM` doesn't exist until after GROUP BY has run.
WHERE vs HAVING — a clear rule
If the condition refers to a column value in the raw row → use WHERE
If the condition refers to an aggregate (SUM, COUNT, AVG, MIN, MAX) → use HAVING
You can use both in the same query: ```sql SELECT ProductCategory, AVG(UnitPrice) AS AvgPrice FROM Products WHERE Discontinued = FALSE -- row filter GROUP BY ProductCategory HAVING AVG(UnitPrice) greater than 50 -- group filter ORDER BY AvgPrice DESC; ```
Business uses
Categories with more than N sales in a period
Customers who placed at least 5 orders
Departments whose total payroll exceeds the budget
Suppliers with average delivery time greater than 7 days (a quality flag)