Friday, July 18, 2008

SQL Server : Sum of 2 querries which has already sum function

There are 2 ways for summing the 2 sub querries:
Method : 1
select
(select SUM(substring(SP_Product, 5, 2) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=8 )
+
(select SUM(substring (SP_Product, 5, 3) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=9 ) AS Muliplied
------------
Method: 2
select Sum(tmp.Muliplied) As Muliplied
From (
select SUM(substring (SP_Product, 5, 2) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=8
union
select SUM(substring (SP_Product, 5, 3) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=9 ) as tmp

No comments: