# 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 November 3, 2026

## 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:

```sql
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:

```sql
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.

```sql
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?

```sql
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

- [HAVING](https://www.ask-codey.com/glossary/having)
- [WHERE](https://www.ask-codey.com/glossary/where-database)
- [Query Order of Execution](https://www.ask-codey.com/glossary/query-order-of-execution)

## Cheat sheets

- [HAVING](https://www.ask-codey.com/cheat-sheets/aggregate-functions-in-t-sql-having-cheat-sheet)

Source: https://www.ask-codey.com/lessons/aggregate-functions-in-t-sql-having