Aggregate functions are not allowed in WHERE: error 1111 and how to rewrite
ERROR 1111 (HY000): Invalid use of group functionShort answer
WHERE filters rows before they are grouped, so it cannot contain COUNT(), SUM(), MAX() and other aggregate functions; MySQL answers with error 1111. Move the test to HAVING when it is about a group, or compare against a scalar subquery when it is about every row.
Fix it fast
1Find out which cause you have
Find the aggregate call (COUNT, SUM, AVG, MIN, MAX) in your WHERE clause, and decide whether you are filtering groups or single rows.
grep -n -i -E "where.*(count|sum|avg|min|max)\(" query.sql2Match what you see, then jump to its fix
- 1
WHERE ... COUNT(*) > 1 or WHERE SUM(total) > 100 with a GROUP BY.A group condition was put in WHERE instead of HAVINGFix - 2
WHERE price = MAX(price), or WHERE salary > AVG(salary), with no GROUP BY.The condition compares each row with an aggregate of the whole tableFix - 3
The error says Unknown column 'cnt' in 'where clause' after an alias was defined as COUNT(*) AS cnt.A column alias of an aggregate was used in WHEREFix
- 1
What does the error look like?
ERROR 1111 (HY000): Invalid use of group functionERROR 1054 (42S22): Unknown column 'cnt' in 'where clause'
Cause 1 of 3
A group condition was put in WHERE instead of HAVING
1Check
WHERE can use any function except aggregate (group) functions. HAVING holds conditions on groups, formed by GROUP BY, and is applied nearly last.
SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer_id;2Fix
Keep row filters in WHERE and move the aggregate test to HAVING.
SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) > 1;3Verify
The query returns one row per customer with more than one paid order.
-- run the corrected query aboveConfirmed by dev.mysql.com
Cause 2 of 3
The condition compares each row with an aggregate of the whole table
1Check
There is no group to apply HAVING to; you want every row compared with one computed value. MySQL's manual shows this as a scalar subquery comparison.
SELECT * FROM products WHERE price = MAX(price);2Fix
Compute the aggregate in a subquery that returns a single value.
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);3Verify
The rows returned all have the highest price.
SELECT MAX(price) FROM products;Confirmed by dev.mysql.com
Cause 3 of 3
A column alias of an aggregate was used in WHERE
1Check
Standard SQL disallows column aliases in WHERE, because the value is not known until after rows are selected and grouped. MySQL lets GROUP BY, ORDER BY and HAVING use the alias.
SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;2Fix
Use the alias in HAVING.
SELECT id, COUNT(*) AS cnt FROM tbl_name
GROUP BY id HAVING cnt > 0;3Verify
The query runs without an unknown-column error.
-- run the corrected query aboveConfirmed by dev.mysql.com
How do I stop it coming back?
- Read WHERE as 'which rows go in' and HAVING as 'which groups come out'.
- Keep non-aggregate filters in WHERE so fewer rows reach the grouping step.
- Compute table-wide values in a subquery and compare against that.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix