I have two tables and get SUM for two columns avalable in two tables.But gives me wrong SUM().
Please find below sample code-
create table #temp1(id int,sal int)
insert into #temp1 values(1,10)
insert into #temp1 values(2,20)
insert into #temp1 values(3,30)
create table #temp2(id int,sal int)
insert into #temp2 values(1,10)
insert into #temp2 values(1,NULL)
insert into #temp2 values(2,30)
select * from #temp1
select * from #temp2
select SUM(sal) from #temp1 where id=1
select SUM(sal) from #temp2 where id=1
select * from #temp1
select * from #temp2
select SUM(a.sal) as Ist,SUM(b.sal) as IInd, SUM(a.sal) + coalesce(SUM(b.sal),0) as TotSal from #temp1 a inner join #temp2 b
on a.id=b.id
where a.id=1
--Why it is 30 in TotSal column.It should be 20.
Loading
Ganesh MPosted Sep 22, 2012, 5:46 AM
select a.id,SUM(a.sal) as Ist,sum(b.IInd) as IId,SUM(a.sal)+SUM(b.IInd) as TotalSal from #temp1 a inner join (select id,coalesce(SUM(sal),0) as IInd from #temp2 where id=1 group by id) as b
on a.id=b.id
where a.id=1 group by a.id