How to fix MySQL invalid use of group function error

When executing a query in MySQL, you may encounter an error saying Invalid use of group function when using aggregate functions like AVG(), SUM(), MAX(), MIN(), and many others.

For example, suppose you have a table named pets that keep the following records:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Peter  | dog     |    5 |
+----+--------+---------+------+

From this table, you are required to query the table and show rows where the age value is smaller than the average age of all rows.

You may write a SELECT statement as follows, which triggers the Invalid use of group function error:

mysql> SELECT * FROM pets WHERE age < AVG(age);

ERROR 1111 (HY000): Invalid use of group function

The error above is because the AVG() function is used inside the WHERE clause.

Aggregate functions like AVG(), COUNT(), MAX(), MIN(), and many others can’t be used in the WHERE() clause

There are two ways you can solve this error in MySQL:

• Wrap the aggregate function call in a subquery
• Use the HAVING clause for the aggregate function call

This tutorial will help you learn how to do both. Let’s start with using the HAVING clause

Fix invalid use of group function with a subquery

When you need to use an aggregate function inside a WHERE clause, you need to wrap the aggregate function call in a subquery.

Returning to the pets table, you can fix the query from this:

SELECT * FROM pets WHERE age < AVG(age);

To this:

SELECT * FROM pets WHERE age < (SELECT AVG(age) FROM pets);

The average age value is 3.6, so the result set will only return rows where age is smaller than that:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
+----+--------+---------+------+

That’s one way you can fix the invalid use of group function error. Next, let’s see how to fix the error using a HAVING clause.

Fix invalid use of group function with HAVING clause

From the same pets table above, suppose you want to find out the average age of the pets and show only pets where the average age value is greater than 2.

You may write a SELECT statement as follows:

SELECT species, AVG(age) FROM pets WHERE AVG(age) > 2 GROUP BY species;

But the query above throws the same error because aggregate functions can’t be used in the WHERE clause.

Instead of using the WHERE clause, you can use the HAVING clause as follows:

SELECT species, AVG(age) FROM pets GROUP BY species HAVING AVG(age) > 2;

Now the query should work and returns the correct result.

Conclusion

The MySQL error Invalid use of group function is caused by aggregate functions in your query that’s placed in the wrong clause.

Most likely you are placing one or more aggregate functions inside the WHERE clause, which won’t work because the WHERE clause filters the table before MySQL actually does the computation.

When you’re using a WHERE clause, the SQL query works like this:

• Filter the rows using the WHERE clause
• Compute the aggregate functions call

When MySQL runs the WHERE clause, the computation of the aggregate functions hasn’t been executed yet, so it throws an error.

When you’re using a subquery MySQL will evaluate the subquery first, so the average age value in the pets table above will be computed before selecting the rows with the WHERE clause.

Finally, the HAVING clause works like the WHERE clause, but it’s executed AFTER the computation has been done:

• Compute the aggregate functions call
• Filter the rows using the HAVING clause

This is why the HAVING clause can have aggregate functions while the WHERE clause can’t.

You’ve just learned how to fix the Invalid use of group function error. Nice work! 👍