SUM() function Issue ?

Question

0.00/5 (No votes)

See more:

I have two table and want to get exact SUM but unable to get-
Please find below sample query-

SQL

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:14am

Member 8090436

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kuthuparakkal · Accepted Answer · 2012-09-13T00:26:00

Solution 1

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:

SQL

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

Posted 13-Sep-12 0:26am

Kuthuparakkal

Updated 13-Sep-12 0:29am

v2

Comments

__TR__ 13-Sep-12 6:36am

5ed!

Kuthuparakkal 13-Sep-12 7:30am

thanks __TR__

__TR__ · Accepted Answer · 2012-09-13T00:36:00

Kuthuparakkal explained why you are getting the sum as 30 and not 20.
The below query will get rid of this issue for you.

SQL

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 :)