Haggan Haggan - 1 year ago 72
SQL Question

SQL 2008 - Call for named formula

So lets say I have this code:

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

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 Source

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(
    todaypositiondate as 
    round(sum(realizedccy*spotsek),0) as Realized
from T1 
    group by todaypositiondate
    order by todaypositiondate desc)

    Realized / 100 as RealizedDivHundred
from cte