> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL HAVING

> How the MySQL HAVING clause filters groups after GROUP BY has built them, what separates it from WHERE, and why it can name a column alias when WHERE cannot.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`WHERE` drops rows before they are grouped. `HAVING` drops whole groups after
they are built. That 1 sentence is the whole lesson, and everything below is a
consequence of it.

Open the client with `mysql -u root -p sakila` before you start.

## How HAVING works

```sql theme={null}
SELECT GROUPING_COLUMN, AGGREGATE_FUNCTION(COLUMN_NAME)
FROM TABLE_NAME
GROUP BY GROUPING_COLUMN
HAVING GROUP_CONDITION;
```

Replace `GROUPING_COLUMN` with the column that defines the groups, `TABLE_NAME`
with the table, `AGGREGATE_FUNCTION(COLUMN_NAME)` with what to work out per
group, and `GROUP_CONDITION` with a test that applies to a whole group, such as
`COUNT(*) > 200`.

The server builds every group first, then keeps the groups where the condition
is true.

## Examples

### 1) Keep only the large groups

```sql theme={null}
SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating
HAVING COUNT(*) > 200
ORDER BY rating;
```

```text theme={null}
+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   223 |
| NC-17  |   210 |
+--------+-------+
2 rows in set (0.01 sec)
```

All 5 ratings were counted. 3 of the groups then failed the test and were
dropped.

### 2) HAVING can use an alias

An alias names a column of the result, and by the time `HAVING` runs the result
exists.

```sql theme={null}
SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating
HAVING films > 200
ORDER BY rating;
```

```text theme={null}
+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   223 |
| NC-17  |   210 |
+--------+-------+
2 rows in set (0.00 sec)
```

The same 2 rows, and shorter to write. `WHERE` cannot do this, because it runs
before there is a result to name: the [WHERE](/docs/tutorial/where) lesson shows the
`ERROR 1054` you get instead.

### 3) Both clauses in one query

Most real queries use both: `WHERE` to choose the rows, `HAVING` to choose the
groups.

```sql theme={null}
SELECT rating, COUNT(*) AS films
FROM film
WHERE length > 120
GROUP BY rating
HAVING COUNT(*) > 100
ORDER BY rating;
```

```text theme={null}
+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   118 |
+--------+-------+
1 row in set (0.00 sec)
```

`WHERE` kept the films over 120 minutes, the grouping counted them per rating,
and `HAVING` kept the 1 rating with more than 100 of them.

## An aggregate cannot go in WHERE

Move that `HAVING` condition into `WHERE` and the server refuses it outright.

```sql theme={null}
SELECT rating, COUNT(*) AS films
FROM film
WHERE COUNT(*) > 200
GROUP BY rating;
```

```text theme={null}
ERROR 1111 (HY000): Invalid use of group function
```

`WHERE` runs before the grouping, so there is no count for it to test. The same
applies to the alias of an aggregate, with a different error.

```sql theme={null}
SELECT rating, COUNT(*) AS films
FROM film
WHERE films > 200
GROUP BY rating;
```

```text theme={null}
ERROR 1054 (42S22): Unknown column 'films' in 'where clause'
```

Both mean the same thing and both are fixed the same way: move the condition to
`HAVING`.

`ERROR 1054` on an alias in `WHERE` has 2 remedies, and the aggregate is what
tells them apart. An alias for a plain expression can be repeated in `WHERE`,
which the [WHERE](/docs/tutorial/where) lesson shows. An alias for an aggregate
cannot be repeated there at all, because of `ERROR 1111` above, so it has to
move to `HAVING`.

## Which clause tests what

| Clause | Runs | Tests | Example |
| - | - | - | - |
| `WHERE` | before grouping | 1 row | `WHERE length > 120` |
| `HAVING` | after grouping | 1 whole group | `HAVING COUNT(*) > 50` |

Put a condition in `WHERE` whenever it tests a row, and keep `HAVING` for what
only makes sense once a group exists. That is also the cheaper arrangement, for
reasons the
[MySQL GROUP BY performance](/docs/guides/group-by-having) guide measures.

<Note>
  Naming a select-list alias in `HAVING` is a MySQL extension. Repeat the
  aggregate rather than the alias when the same SQL has to run on another
  database.
</Note>

## Summary

* Use `HAVING` to drop whole groups, and `WHERE` to drop rows.
* Expect `HAVING` to run after `GROUP BY`, so it can test an aggregate.
* Read `ERROR 1111` as an aggregate in `WHERE` that belongs in `HAVING`.
* Name a column alias in `HAVING`, which `WHERE` cannot do.
* Put a condition in `WHERE` when it tests a row rather than a group.

## See also

* [GROUP BY](/docs/tutorial/group-by) — building the groups this clause filters
* [WHERE](/docs/tutorial/where) — the clause that runs first
* [Aggregate functions](/docs/tutorial/aggregate-functions) — what a group condition usually tests
* [MySQL GROUP BY performance](/docs/guides/group-by-having) — the cost of grouping before filtering