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