CODEWITHSUNDEEP
sql
code_martial
code_martial

Reputation: 1245

If I use an alias in a SELECT clause, how do I refer back to that alias?

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?

Upvotes: 8

Views: 2862

Answers (2)

John N
John N

Reputation: 1815

I would usually handle this with a sub-query:

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

If you've got SQL Server, a CTE achieves much the same thing.

Upvotes: 3

triclosan
triclosan

Reputation: 5714

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;

Upvotes: 9

Related Questions