CODEWITHSUNDEEP
mysqlsql
user3312802
user3312802

Reputation: 19

Mysql alias in where condition

This is my SQL CODE:

SELECT `qty`,`unitprice`, qty * unitprice as Stock FROM item where Stock<1000

but it is saying:

#1054 - Unknown column 'Stock' in 'where clause'

how to fix it?

Upvotes: 0

Views: 158

Answers (3)

Revan
Revan

Reputation: 1144

By adding parentheses MySQL calculates the new value first. If not there will be a syntax error

SELECT qty,unitprice, (qty * unitprice) as Stock 
FROM item where (qty * unitprice)<1000

Upvotes: 0

toddsonofodin
toddsonofodin

Reputation: 513

You can't have an alias in a where condition. It will only be exposed to the next layer up - the calling application, or a parent query if this is written as a subquery.

where qty * unitprice < 1000

Upvotes: 0

eggyal
eggyal

Reputation: 125835

As documented under Problems with Column Aliases:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

You must instead repeat the calculation within your WHERE clause:

SELECT qty, unitprice, qty * unitprice as Stock
FROM   item
WHERE  qty * unitprice < 1000

Upvotes: 3

Related Questions