Mangesh barmate

Mangesh barmate

  • NA
  • 118
  • 133.5k

SUM() function issue ?

Sep 13 2012 6:15 AM
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.

Answers (1)