CODEWITHSUNDEEP
sqlsql-serversql-server-2012
sam360
sam360

Reputation: 1131

SQL: Referencing calculated columns in other parts of the query

I would like to do the following in SQL Server 2012:

SELECT UnitPrice * Quantity as SubTotal,
       SubTotal * (1-0.13) as Tax,
       Tax + SubTotal as Total
FROM Invoice

As far as I know, this is not possible to do as written above, however I was wondering if there is a special way of identifying the column on the fly which could be referenced elsewhere in the query such as "Field list" (part of SELECT clause), as part of WHERE clause or in the ORDER BY clause.

Any help would be greatly appreciated.

Upvotes: 2

Views: 577

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can do the calculations in a cross apply.

select T1.SubTotal,
       T2.Tax,
       T2.Tax + T1.SubTotal as Total
from Invoice as I
  cross apply (select I.UnitPrice * I.Quantity) as T1(SubTotal)
  cross apply (select T1.SubTotal * (1 - 0.13)) as T2(Tax)

SQL Fiddle

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186718

Partial solution (which is enough in many cases, however) is with construction:

with Query as (
  select SubTotal as SubTotal,
         SubTotal * (1 - 0.13) as Tax,
         SubTotal * (1 - 0.15) as AnotherTax -- etc.
    from Invoice
)

select SubTotal,
       Tax,
       Tax + SubTotal as Total
  from Query

Upvotes: 4

Related Questions