MySQL HAVING

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

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

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

SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating
HAVING COUNT(*) > 200
ORDER BY rating;

+--------+-------+
| 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.

SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating
HAVING films > 200
ORDER BY rating;

+--------+-------+
| 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 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.

SELECT rating, COUNT(*) AS films
FROM film
WHERE length > 120
GROUP BY rating
HAVING COUNT(*) > 100
ORDER BY rating;

+--------+-------+
| 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.

SELECT rating, COUNT(*) AS films
FROM film
WHERE COUNT(*) > 200
GROUP BY rating;

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.

SELECT rating, COUNT(*) AS films
FROM film
WHERE films > 200
GROUP BY rating;

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 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 guide measures.

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.

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.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How HAVING works

Examples

1) Keep only the large groups

2) HAVING can use an alias

3) Both clauses in one query

An aggregate cannot go in WHERE

Which clause tests what

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How HAVING works

​Examples

​1) Keep only the large groups

​2) HAVING can use an alias

​3) Both clauses in one query

​An aggregate cannot go in WHERE

​Which clause tests what

​Summary

​See also

How HAVING works

Examples

1) Keep only the large groups

2) HAVING can use an alias

3) Both clauses in one query

An aggregate cannot go in WHERE

Which clause tests what

Summary

See also