Referencing calculated column in another calculation in same query

1

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

2

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 
--------------------------------------------------------------------------------------------
3

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

4

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
5

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.

6

Thanks a lot Jeff!!