Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL does not know a column alias used in the WHERE clause

ErrorERROR 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).

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1Unknown column 'total' in 'where clause' and total is defined in the SELECT listThe condition is a plain row filter on a computed valueFix
    2. 2The alias is COUNT(*) or SUM(...) from a GROUP BY queryThe condition is on an aggregate, so it belongs in HAVINGFix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.