Problems with SUM and JOINs - 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

General SQL Server Forums

New to SQL Server Programming

Problems with SUM and JOINs

Author

Topic

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 04:06:29

Hi

I'm working on a project where I'm comparing two transaction tables with eachother, where one has a lot of missing entries. The tables look something like this:

My problem here is that I want to SUM each orders 'amount' column and join the two tables with eachother. When doing this and TABLE1 has two entires (for example for order 102030 i get sum(amount)=50 but then sum(amount2)=200 because the value comes double since there are two rows in TABLE1 (and the join puts amount2s 100 in two times). How do I best solve this problem?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-31 : 04:28:16

can you also post the query which you have used?

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 04:31:02

To clarify somewhat:
I want the result to look like this after a join of TABLE1,TABLE2:

Or if there's an easier way to do it. From this new table I want to SUM amount and amount2.

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 04:37:43

quote:
Originally posted by MIK_2008

can you also post the query which you have used?

SELECT  
onr, sum(totalA) AS total_1,sum(totalB) AS total_2,(sum(totalA)-sum(totalB)) AS diff 
FROM
(SELECT totalA = CASE t1.[type] WHEN 'R' THEN (t1.amount*-1) ELSE t1.amount END, t1.[order] AS onr FROM TransactionTable1 t1) amounts -- type=R = return
LEFT OUTER JOIN 
(select amount as totalB,[order] as onr2 FROM TransactionTable2 t2) payments ON -- no conversion to negative value needed here
amounts.onr=payments.onr2 
GROUP BY onr 
HAVING (sum(totalA)-sum(totalB))<>0 order by onr

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-31 : 05:07:58

the output you asked for could be fetched using the below. Check it out

Select
Order#1,Amount,table1.[Type],Amount2
From Table1
Left Join Table2 on Table1.Order#1=Table2.Order#2 and Table1.[Type]=Table2.[Type]

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 05:14:08

quote:
Originally posted by MIK_2008

the output you asked for could be fetched using the below. Check it out

Select
Order#1,Amount,table1.[Type],Amount2
From Table1
Left Join Table2 on Table1.Order#1=Table2.Order#2 and Table1.[Type]=Table2.[Type]

Ok I understand, but I think I was a bit unclear. It can also look like this (for one single order number):


Amount1 | Type1          Amount2 | Type2
100       sales          100       sales
200       sales

So the 'type' column really doesnt have anything to do with the outcome I want. If there are 2 rows in table1, regardless of if they're sales/returns and 1 row in table2 I still don't want sum(amount2) to double up on me

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-31 : 05:24:27

Just A guess: i think you are looking for this output?

Select
Order#1,sum(Amount),sum(Amount2)
From Table1
Left Join Table2 on Table1.Order#1=Table2.Order#2
Group by Order#1

well if not then post the required output of the given data as per original post. as i am not that good to understand the details mentioned in your last response!~

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 05:33:50

quote:
Originally posted by MIK_2008

well post the required output of the given data as per original post. as i am not that good to understand the details mentioned in your last response!~

Of course

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-31 : 05:56:57

For this i have already suggested a query... Any problem in that one ? did you try ?

Select
Order#1,Amount,table1.[Type],Amount2
From Table1
Left Join Table2 on Table1.Order#1=Table2.Order#2 and Table1.[Type]=Table2.[Type]

--With Example
Declare @table1 table(order1 int,amount int,type1 varchar(10))
Declare @table2 table(order2 int,amount2 int,type2 varchar(10))

Insert into @table1 values (102030,100,'Sale'),(102030,-50,'Return')
Insert into @table2 values (102030,100,'Sale')

Select
Order1,amount,type1,amount2
From @Table1
Left Join @Table2
on Order1=Order2
and Type1=Type2

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 06:07:05

quote:
Originally posted by MIK_2008

For this i have already suggested a query... Any problem in that one ? did you try ?

Select
Order#1,Amount,table1.[Type],Amount2
From Table1
Left Join Table2 on Table1.Order#1=Table2.Order#2 and Table1.[Type]=Table2.[Type]

--With Example
Declare @table1 table(order1 int,amount int,type1 varchar(10))
Declare @table2 table(order2 int,amount2 int,type2 varchar(10))

Insert into @table1 values (102030,100,'Sale'),(102030,-50,'Return')
Insert into @table2 values (102030,100,'Sale')

Select
Order1,amount,type1,amount2
From @Table1
Left Join @Table2
on Order1=Order2
and Type1=Type2

Yes, but I also need to SUM amount1 and amount2 for each order (see my posted query)

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 06:17:37

And when I SUM I can't use Type to group by. I don't want the type to be in the result.. Just the ordernumber and the SUM of amount1 and the SUM of amount2

mauich123
Starting Member

21 Posts

Posted - 2011-01-31 : 06:41:49

I solved it :) Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources