Click here to Skip to main content
Search within:



Next

SUM() function Issue ?
Sign Up to vote bad
good
See more: SQL-Server
I have two table and want to get exact SUM but unable to get-
Please find below sample query-
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.
Posted 13 Sep '12 - 0:14
Member 8090436827
Add a Solution


2 solutions

Because your Id repeats on second table. So when you inner join, you get two rows from temp2 for id=1 thats sum 20, and sum =10 on your first table...
 
Simple explanation can be:
select * from #temp1 a
 inner join #temp2 b
on a.id=b.id
where a.id=1
 
id sal id sal
1 10 1 10
1 10 1 NULL
-----------------------------
SUM(a.sal)= 20 SUM(b.sal) = 10
Total 30
  Permalink  
Posted 13 Sep '12 - 0:26
Kuthuparakkal17K
Edited 13 Sep '12 - 0:29
v2
Comments
__TR__ - 13 Sep '12 - 6:36
5ed!
Kuthuparakkal - 13 Sep '12 - 7:30
thanks __TR__
Kuthuparakkal explained why you are getting the sum as 30 and not 20.
The below query will get rid of this issue for you.
SELECT Id, Sal_1 + Sal_2 AS TotSal FROM
(
    SELECT T1.Id, Sal_1,Sal_2  FROM
    (
        SELECT Id, SUM(Sal) AS Sal_1 FROM #temp1
        GROUP BY Id
    )T1
    INNER JOIN
    (
        SELECT Id, SUM(Sal) AS Sal_2 FROM #temp2
        GROUP BY Id
    ) T2 ON T1.Id = T2.Id
) T
 
Hope this helps Smile | :)
  Permalink  
Posted 13 Sep '12 - 0:36
__TR__28.5K
Comments
Mohamed Mitwalli - 13 Sep '12 - 14:09
5+
__TR__ - 14 Sep '12 - 2:09
Thanks :)
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 213
1 Sergey Alexandrovich Kryukov 159
2 Richard MacCutchan 150
3 Maciej Los 136
4 Tadit Dash 110
0 Sergey Alexandrovich Kryukov 10,264
1 OriginalGriff 7,937
2 CPallini 4,201
3 Rohan Leuva 3,522
4 Maciej Los 3,135


Advertise | Privacy | Mobile
Web01 | 2.6.130523.1 | Last Updated 13 Sep 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid