MySQL Alias Help ERROR 1054 (42S22)

Question

I will keep this brief and simple. I want to perform this query:

mysql> SELECT ORDER_NUM, NUM_ORDERED * QUOTED_PRICE AS TOTAL_AMOUNT
    -> FROM ORDER_LINE
    -> WHERE TOTAL_AMOUNT > '1000'
    -> ORDER BY ORDER_NUM;
ERROR 1054 (42S22): Unknown column 'TOTAL_AMOUNT' in 'where clause'

I'm sure it's something simple but I don't understand why this wont work using the alias TOTAL_AMOUNT. Any help is appreciated!

Answer 1

Check if that asterisk is not causing you problems or if you are missing a comma and if that does not work, enclose the alias between grave accent simbols like this:

... QUOTED_PRICE AS `TOTAL_AMOUNT`...

Answer 2

try this

mysql> SELECT ORDER_NUM, NUM_ORDERED * QUOTED_PRICE AS TOTAL_AMOUNT
-> FROM ORDER_LINE
-> WHERE NUM_ORDERED * QUOTED_PRICE > '1000'
-> ORDER BY ORDER_NUM;