8

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?

code_martial
  • 1,185
  • 1
  • 11
  • 23

2 Answers2

9
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;
triclosan
  • 5,578
  • 6
  • 26
  • 50
3

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.

John N
  • 1,755
  • 17
  • 21
  • The same problem arises with the subquery. What you probably meant was to move "Foo - col_c as Bar" bit to the outer query. I'm not sure if a subquery will be optimal in this context, though. – code_martial Aug 19 '11 at 10:44
  • Correct. @codie: See this similar question for an explanation: http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685 – ypercubeᵀᴹ Aug 19 '11 at 10:44
  • @codie: you either use the subquery solution or you repeat the code in the `WHERE` clause. – ypercubeᵀᴹ Aug 19 '11 at 10:45
  • @codie - Bear in mind that in this case the Sub-Query is an Inline-View. It is expanded when compiled and *should* perform the same as if you did not use this 'trick' to avoid repeating yourself in the code (by using the aliases definitions in the outer WHERE clause or using them to achieve `Foo - col_c`). – MatBailie Aug 19 '11 at 10:48
  • Ah, apologies, error stems from not reading your question properly. The principal remains the same, however, and triclosan's answer gives what you want. – John N Aug 19 '11 at 10:55