SUM - SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM - SUM

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-25 : 13:53:38

I am trying to get the accumulative SUM from entries in two tables:

TBL1

no --------- count

1A23B4 --------- 4
1A23B4 --------- 1
1A23B4 ---------(-1)
1A23B4 --------- (-1)
1A23B4 --------- 1

TBL2

no --------- count

1A23B4 --------- 4

I would like to subtract the SUM of TBL2 from the SUM of TBL1, like

SELECT
a.no
, sum(a.count) - sum(b.count)
FROM TBL1 a
inner join TBL2 b
ON a.no = b.no
GROUP BY a.no

So with the example above I would expect SUM(a.count) to be 4
and SUM(b.count) to be 2, so my result should be 2.

But I think I'm missing the grouping for TBL2 because my inner join returns 5 rows from TBL1 therefore I have 5 results for the count value of 2 int TBL2 so TBL2 sum(count) give me a result od 10 so my SUM-SUM gives me a result of -8.

How can I rectify this?

Thanks for your help. -KE

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-25 : 14:01:43

Why should sum(b.count) be 2 ???
I think it would be 4.

Ok - maybe this is a typo.

Your solution should be to group (and sum) each table and join the grouped results (= derieved tables) and then do the substract.

No, you're never too old to Yak'n'Roll if you're too young to die.

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-25 : 14:56:38

I'm not familiar with the use of derived tables. Would you mind explaining?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-25 : 16:02:04

Like this:

select dt1.no, dt1.cnt - dt2.cnt
from
(select no, sum(count) as cnt from tbl1 group by no) as dt1
join
(select no, sum(count) as cnt from tbl2 group by no) as dt2
on dt1.no=dt2.no

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 16:33:50

You can also use UNION ALL instead of JOIN

-- Ver 1
SELECT		[No],
		SUM([Count])
FROM		(
			SELECT		[No],
					SUM([Count]) AS [Count]
			FROM		Tbl1
			GROUP BY	[No]

			UNION ALL

			SELECT		[No],
					-SUM([Count]) AS [Count]
			FROM		Tbl1
			GROUP BY	[No]
		) AS d
GROUP BY	[No]

-- Ver 2
SELECT		[No],
		SUM([Count])
FROM		(
			SELECT	[No],
				[Count]
			FROM	Tbl1

			UNION ALL

			SELECT	[No],
				-[Count]
			FROM	Tbl1
		) AS d
GROUP BY	[No]

N 56°04'39.26"
E 12°55'05.63"

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-08-26 : 09:52:21

Great - thanks.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-28 : 01:46:22

You have to clarify whether Table2 is 2 or 4.
Asuming that table2 is 2 and using the values from table1 where the sum is 4 then as stated in your problem , the accumilated sum should be 6.

DECLARE @Tbl1 TABLE
(
No INT,
Count smallint
)

Declare @Tbl2 Table
(
No int,
Count smallint
)

insert into @Tbl1
select 1A23B4, 4 union all
select 1A23B4, 1 union all
select 1A23B4, -1 union all
select 1A23B4, -1 union all
select 1A23B4, 1

insert into @Tbl2
select 1A23B4, 2

select dt1.no, dt1.cnt + dt2.cnt
from
(
select no, sum(count) as cnt
from @Tbl1
group by no
) as dt1
join
(
select no, sum(count) as cnt
from @Tbl2
group by no
) as dt2
on dt1.no = dt2.no
----------------------------
(5 row(s) affected)

(1 row(s) affected)
no
----------- -----------
1 6

(1 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources