SUM()函数问题? [英] SUM() function Issue ?

问题描述

我有两个表，想获取精确的SUM，但无法获取-
请在下面的示例查询中找到

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.

推荐答案

因为您的ID在第二张表上重复.因此，当您进行内部联接时，您将从temp2中获得两行，其中id = 1，即总和20，在第一个表上总和= 10 ...

简单的解释可以是:

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
总数30

id sal id sal
1 10 1 10
1 10 1 NULL
-----------------------------
SUM(a.sal)= 20 SUM(b.sal) = 10
Total 30

Kuthuparakkal解释了为什么将总和设为30而不是20.
以下查询将为您解决此问题.

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

这篇关于SUM()函数问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！