Introduction

The MySQL error "Invalid use of group function" (Error 1111) occurs when you use an aggregate function like SUM(), COUNT(), AVG(), MAX(), or MIN() in a WHERE clause instead of a HAVING clause. WHERE filters individual rows before grouping, so aggregate functions are not available there. HAVING filters groups after aggregation, which is where aggregate conditions belong. The fix is almost always to move the aggregate condition from WHERE to HAVING, or to use a subquery.

The Error

1-- ERROR 1111: Invalid use of group function
2SELECT department, COUNT(*)
3FROM employees
4WHERE COUNT(*) > 5
5GROUP BY department;

MySQL cannot evaluate COUNT(*) in the WHERE clause because WHERE runs before the GROUP BY operation. At that point, groups have not been formed yet, so COUNT(*) has no meaning.

The Fix: Use HAVING

1-- CORRECT — use HAVING for aggregate conditions
2SELECT department, COUNT(*) AS emp_count
3FROM employees
4GROUP BY department
5HAVING COUNT(*) > 5;

HAVING runs after GROUP BY, so aggregate functions are available.

WHERE vs HAVING

SQL Execution Order

11. FROM / JOIN         — identify tables
22. WHERE               — filter rows
33. GROUP BY            — form groups
44. aggregate functions — compute SUM, COUNT, etc.
55. HAVING              — filter groups
66. SELECT              — choose columns
77. ORDER BY            — sort results
88. LIMIT               — restrict row count

Because WHERE (step 2) runs before GROUP BY (step 3) and aggregation (step 4), aggregate functions are not available in WHERE.

Common Scenarios

Filtering by Aggregate Count

1-- WRONG
2SELECT product_id, SUM(quantity)
3FROM order_items
4WHERE SUM(quantity) > 100
5GROUP BY product_id;
6
7-- CORRECT
8SELECT product_id, SUM(quantity) AS total_qty
9FROM order_items
10GROUP BY product_id
11HAVING SUM(quantity) > 100;

Filtering by Average

1-- WRONG
2SELECT department, AVG(salary)
3FROM employees
4WHERE AVG(salary) > 50000
5GROUP BY department;
6
7-- CORRECT
8SELECT department, AVG(salary) AS avg_salary
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Combining WHERE and HAVING

You can use both clauses — WHERE for row-level filters and HAVING for group-level filters:

1-- Filter active employees (row-level), then find departments
2-- with more than 3 active employees (group-level)
3SELECT department, COUNT(*) AS active_count
4FROM employees
5WHERE status = 'active'        -- Row filter: before grouping
6GROUP BY department
7HAVING COUNT(*) > 3;           -- Group filter: after grouping

Aggregate in a Subquery

When you need to filter rows based on an aggregate from a related query, use a subquery:

1-- Find employees whose salary exceeds their department's average
2SELECT e.name, e.salary, e.department
3FROM employees e
4WHERE e.salary > (
5    SELECT AVG(e2.salary)
6    FROM employees e2
7    WHERE e2.department = e.department
8);

Nested Aggregate Error

1-- WRONG — MAX(COUNT(*)) is a nested aggregate, not allowed in most contexts
2SELECT department, MAX(COUNT(*))
3FROM employees
4GROUP BY department;
5
6-- CORRECT — use a subquery
7SELECT MAX(dept_count) AS max_count
8FROM (
9    SELECT department, COUNT(*) AS dept_count
10    FROM employees
11    GROUP BY department
12) AS dept_counts;

Using Aggregate in UPDATE and DELETE

The same error occurs in UPDATE and DELETE statements:

1-- WRONG
2DELETE FROM orders
3WHERE total > AVG(total);
4
5-- CORRECT — use a subquery
6DELETE FROM orders
7WHERE total > (SELECT AVG(total) FROM (SELECT total FROM orders) AS t);
8
9-- WRONG
10UPDATE products
11SET featured = 1
12WHERE sales_count > AVG(sales_count);
13
14-- CORRECT — use a subquery
15UPDATE products
16SET featured = 1
17WHERE sales_count > (SELECT avg_sales FROM (
18    SELECT AVG(sales_count) AS avg_sales FROM products
19) AS t);

Note the double subquery in MySQL — MySQL does not allow UPDATE/DELETE to directly reference the same table in a subquery, so an extra level of nesting is needed.

Common Pitfalls

• Putting aggregate conditions in WHERE instead of HAVING: This is the primary cause of Error 1111. Remember that WHERE filters rows before grouping and cannot use aggregate functions. Move aggregate conditions to HAVING.
• Referencing column aliases in HAVING: HAVING avg_salary > 50000 works in MySQL but is not standard SQL. For portability, repeat the expression: HAVING AVG(salary) > 50000. In strict SQL mode or other databases, aliases in HAVING may not work.
• Nesting aggregate functions like MAX(COUNT(*)): MySQL does not support nested aggregates in a single query. Wrap the inner aggregate in a subquery and apply the outer aggregate to the result.
• Forgetting GROUP BY when using HAVING: HAVING without GROUP BY treats the entire table as one group. While syntactically valid, this is rarely intentional and often indicates a missing GROUP BY clause.
• Using HAVING without an aggregate: HAVING status = 'active' works but is slower than WHERE status = 'active' because HAVING runs after grouping. Non-aggregate conditions should always go in WHERE for performance.

Summary

• Error 1111 means an aggregate function (COUNT, SUM, AVG, MAX, MIN) is used in a WHERE clause where it is not allowed
• Move aggregate conditions to HAVING, which runs after GROUP BY
• Use WHERE for row-level filters and HAVING for group-level filters
• For row-level comparisons against aggregates, use a subquery in the WHERE clause
• SQL execution order is FROM > WHERE > GROUP BY > aggregation > HAVING > SELECT > ORDER BY