Sql server uses computed column

Question

I have a query like this:

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

How can I use the calculated column total_price to calculate other totals like this?

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

Is it possible?

Answer 1

There is no reference to a column alias defined at the same level. Expressions that appear in the same phase of processing logical queries are evaluated as at the same time .

As Joe Selco says

Everything happens "all at once" in SQL, and not "from left to right", as they will be in a sequential file / procedural language model

You can define it in the CTE and then reuse it outside the CTE.

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 

Answer 2

I would also consider a computed column in a table if this is often used.

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

Then your request

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

So you can apply the DRY principle ...

Answer 3

 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)