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

Question

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 the Foo error message unknown. Since Foo is derived from some calculations in a bunch of other columns, I don't want to repeat the formula for Bar again. Any workarounds?

Answer 1

 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; 

Answer 2

I would usually handle this with a sub-request:

 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 have SQL Server, CTE does almost the same thing.