sql server use computed column

Question

I have a query like this:

select 
(price1 + price2 + price3) as total_price 
from prices

How can i use the computed column total_price to compute other total like this?

select 
(price1 + price2 + price3) as total_price,
(price4 + total_price) as total_price2
from prices    

Is this possible?

Answer 1

select T.total_price,
       P.price4 + T.total_price as total_price2
from prices as P
  cross apply (select P.price1 + P.price2 + P.price3) as T(total_price)

Answer 2

I'd also consider a computed column on the table if this will used often

ALTER  TABLE prices ADD
   total_price AS (price1 + price2 + price3)

Then your query is

select 
    total_price,
    (price4 + total_price) as total_price2
from prices

This way, you can apply the DRY principle...

Answer 3

No it isn't possible to reference the column alias defined at the same level. Expressions that appear in the same logical query processing phase are evaluated as if at the same point in time.

As Joe Celko says

Things happen "all at once" in SQL, not "from left to right" as they would in a sequential file/procedural language model

You can define it in a CTE and then re-use it outside the CTE however.

Example

WITH T
     AS (SELECT ( price1 + price2 + price3 ) AS total_price,
                price4
         FROM   prices)
SELECT total_price,
       ( price4 + total_price ) AS total_price2
FROM   T