 Referencing calculated column in another calculation in same query

Select A.location, (select sum(col1) from table1 t1 where condition AND t1.location = A.location) as calc1,(select sum(col2) from table2 t2 where conditions given AND t2.location = A.location) as calc2,calc1/calc2 from table1 left join table 2 on table1.col6=table2.col7 group by A.location

I want something like above to work in query but when referencing calc1/calc2 it's unable to recognise the columns.plz help

hi

-----------------------------------------------------------------------------------------------------------
one way is ... you cannot use calcColumn1 and calcColumn2 in same query but
you can use their formula like this
select
(a+b) as calcColumn1 ,
( d+f) as calcColumn2 ,
(a+b)/(d+f)
---------------------------------------------------------------------------------------------------------
another way is CTE
; with cte as
(
select
(a+b) as calcColumn1 ,
( d+f) as calcColumn2 ,
)
select calcColumn1/calcColumn2  from cte
--------------------------------------------------------------------------------------------

Thanks Haish for suggestion but i tried both of them,it's making the query quite slow.

one way to speed up query is temp tables ( also called hash tables #abc )

select (a+b) as calcColumn1 , (d+f) as calcColumn2
into #def

select calcColumn1/calcColumn2  from #def

I prefer using CROSS APPLY for calculations:

Select A.location
, c1.calc1 / c2.calc2
From table1
Left Join Table 2 On table1.col6 = table2.col7

Cross Apply (Select calc1 = sum(col1)
From table1 t1
Where t1.location = a.location
And {other conditions}
) As c1

Cross Apply (Select calc2 = sum(col2)
From table2 t2
Where t2.location = a.location
And {other conditions}
) As c2

Group By
A.location;

With CROSS APPLY - you can even reference the first calculation in the second apply for even more complex calculations.

Thanks a lot Jeff!!