Menu
I want to do something like this:
SELECT round(100*(col_a - col_b)/col_a, 1) as Foo, Foo - col_c as Bar
FROM my_table
WHERE...;
However, I get an error saying Foo is unknown. Since Foo is derived from some calculations on a bunch of other columns, I don't want to repeat the formula again for Bar. Any work-arounds?
356
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated.
column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause. 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.
Alias is used to give a temporary name(only for the duration of the query) to the column or table in order to make the column name or table name more readable. It does not change the name of the column permanently.
In MySQL you CAN reference a select column alias in the same select, as long as it precedes the reference point.
SELECT Foo, Foo - col_c as Bar
from (
SELECT round(100*(col_a - col_b)/col_a, 1) as Foo, col_c
FROM my_table
WHERE...
) t;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
