SQL 2008 - Call for named formula

Question

So lets say I have this code:

Select
 'Position Date' =              todaypositiondate
,'Realized' =           round(sum(realizedccy*spotsek),0)

FROM T1 
    group by todaypositiondate
    order by todaypositiondate desc

If I wan to divide 'Realized' with 100. How can I set 'new' = 'Realized'/100 instead of 'new' = (round(sum(realizedccy*spotsek),0))/100 ?

Best regards

Answer 1

Not sure why you want to do that but here is one way using a CTE. You can also use subqueries, but both of these steps are pretty pointless for the math you want to do, and will decrease performance. You can't reference a column alias by it's alias within the same statement like you are trying to do. This includes referencing it in the where or group by or order by clauses

;with cte as(
Select
    todaypositiondate as 
    round(sum(realizedccy*spotsek),0) as Realized
from T1 
    group by todaypositiondate
    order by todaypositiondate desc)

select 
    todaypositiondate,
    Realized,
    Realized / 100 as RealizedDivHundred
from cte