HAVING

WHERE filters rows before they're grouped; HAVING filters the groups afterward, by their aggregates. Know which to use, and the order a query's clauses run in.

Journeyman · T-SQL · SQL · Updated

After this lesson you can

  • Filter groups by an aggregate with HAVING
  • Choose between WHERE and HAVING, and put a query's clauses in order

Key takeaways

  • HAVING filters the groups after GROUP BY has made them, by their aggregates, as in HAVING COUNT(*) >= 9.
  • WHERE filters rows before they're grouped, so it can't use aggregates: WHERE COUNT(*) > 5 is an error.
  • One query can use both: WHERE picks the rows to count, and HAVING picks the groups to keep.
  • SQL Server runs the clauses in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, not the order they're written in.
  • ORDER BY runs after the SELECT, so it can use the column aliases.

Which countries have nine customers or more? You need to count the customers in each country, then keep only the big counts. WHERE can't do it, because WHERE looks at one row at a time, before any counting has happened. HAVING filters the groups, after they're counted:

SELECT Country, COUNT(*) AS Customers
FROM Customers
GROUP BY Country
HAVING COUNT(*) >= 9
ORDER BY Customers DESC;
Country Customers
USA 13
France 11
Germany 11
Brazil 9

WHERE or HAVING

The two look alike, and give different answers. Compare:

SELECT CategoryId, COUNT(*) AS Products
FROM Products
WHERE UnitPrice > 30
GROUP BY CategoryId
ORDER BY CategoryId;

This keeps the products over $30, then counts them per category: every category has at least two such products, so all eight categories appear.

SELECT CategoryId, AVG(UnitPrice) AS AveragePrice
FROM Products
GROUP BY CategoryId
HAVING AVG(UnitPrice) > 30
ORDER BY CategoryId;
CategoryId AveragePrice
1 37.9791
6 54.0066
7 32.37

This keeps the categories whose average price is over $30: three of them.

WHERE HAVING
Filters Rows, one at a time Groups, after aggregating
Runs Before GROUP BY After GROUP BY
Can use aggregates No: WHERE COUNT(*) > 5 is an error Yes: that's its job
Example WHERE Country = 'USA' HAVING COUNT(*) >= 9

Use both in one query when you need to: WHERE picks the rows to count, HAVING picks the groups to keep. Which employees took more than 40 orders in 2017?

SELECT EmployeeId, COUNT(*) AS Orders
FROM Orders
WHERE YEAR(OrderDate) = 2017
GROUP BY EmployeeId
HAVING COUNT(*) > 40
ORDER BY Orders DESC;
EmployeeId Orders
4 81
3 71
1 55
8 54
2 41

The order of the clauses

A query's clauses are always written in this order, and SQL Server runs them in a different one:

Written Runs Does
1. SELECT 5th Picks the columns and aggregates
2. FROM 1st Picks the table
3. WHERE 2nd Keeps the rows that match
4. GROUP BY 3rd Makes the groups
5. HAVING 4th Keeps the groups that match
6. ORDER BY 6th Sorts the result

That run order explains the rules: WHERE runs before the groups exist, so it can't use aggregates; and ORDER BY runs after the SELECT, so it can use the column aliases.

Check yourself

2 questions to try on the lesson page.

So what

WHERE decides which rows are counted; HAVING decides which totals are worth showing. Together with GROUP BY, they turn "how many of each" into "which ones stand out".

Key terms

Cheat sheets