MySQL does not know a column alias used in the WHERE clause
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'Short answer
WHERE is evaluated before the SELECT list, so the alias does not exist yet. Standard SQL forbids it. Repeat the expression in WHERE, or move the condition to HAVING (aliases are allowed there, and in GROUP BY and ORDER BY).
Fix it fast
1Find out which cause you have
Find the alias in your SELECT list and check where else you use it. If it appears in WHERE, that is the error.
SELECT price * qty AS total FROM order_lines WHERE total > 100;2Match what you see, then jump to its fix
What does the error look like?
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'ER_BAD_FIELD_ERROR: Unknown column '%s' in '%s'
Cause 1 of 2
The condition is a plain row filter on a computed value
1Check
The manual says the value of an alias may not be known when WHERE runs, which is why WHERE cannot use it.
SELECT price * qty AS total FROM order_lines WHERE price * qty > 100;2Fix
Repeat the expression in WHERE. It is the simplest fix.
SELECT price * qty AS total
FROM order_lines
WHERE price * qty > 100;3Verify
The query returns rows with no error.
EXPLAIN SELECT price * qty AS total FROM order_lines WHERE price * qty > 100;Confirmed by dev.mysql.com
Cause 2 of 2
The condition is on an aggregate, so it belongs in HAVING
1Check
The manual's own example shows WHERE cnt > 0 failing with GROUP BY, while HAVING cnt > 0 is allowed; HAVING runs after grouping.
SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id HAVING cnt > 1;2Fix
Move the filter on the aggregate into HAVING and keep row filters in WHERE.
SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING cnt > 1;3Verify
Only customers with more than one paid order come back.
SELECT customer_id, COUNT(*) AS cnt FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING cnt > 1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Remember the order: WHERE filters rows, GROUP BY groups, HAVING filters groups, then the alias exists for ORDER BY.
- Keep long expressions in one place, such as a view, so you do not repeat them across queries.
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
The column named in the statement is not found in that table (error 1054)
ERROR 1054 (42S22): Unknown column 'email' in 'where clause'
Error 1054 means the server read a word as a column name and found no such column in that place.
3 causes, each with a fixDatabases
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 fix