SUM problem with joins. Getting wrong values

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 2000 Forums

Transact-SQL (2000)

SUM problem with joins. Getting wrong values

Author

Topic

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 11:12:35

Hello there. I'm getting unexpected results from a query that I thought was working well.

I have three tables:
tbl_Orders
Order_ID int
Total money

tbl_OrdersAdditions
Order_ID int
Total money

tbl_Payments
Order_ID int
PaymentAmount money

What I'm trying to do is add the SUM of tbl_Orders.Total (should only be one record) and the SUM of tbl_OrdersAdditions.Total (this may have multiple records) and the subtract the SUM of tbl_Payments.PaymentAmount to get the balance owing.

Here's my query:

SET NOCOUNT ON

SELECT ((SUM(ISNULL(t.Total,0)) + SUM(ISNULL(f.Total,0))) - ( SUM(ISNULL(p.PaymentAmount,0)) )) AS Owing

FROM tbl_Orders t

LEFT JOIN tbl_OrdersAdditions f
ON f.Order_ID = t.Order_ID

LEFT JOIN tbl_Payments p
ON p.Order_ID = t.Order_ID

WHERE t.Order_ID = 87750

SET NOCOUNT OFF

I thought this would be pretty straight forward, however it's not working.

The sum from tbl_Orders is: 1017.6
Sum from tbl_OrdersAdditions is: 471.7
Sum of tbl_Payments is: 1419.3

The balance owing should be 70 ((1017.6 + 471.7) - 1419.3), however the result I get is 1559.3.

Please tell me what I am doing wrong. I'm sure this can be done in one simple query. Sadly I'm just not a very good db developer.

Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-13 : 11:54:06

This two-part article should help you:

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

- Jeff
http://weblogs.sqlteam.com/JeffS

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 14:41:10

jsmith, I appreciate your "teach a man to fish" mentality however I was really looking for an answer rather than links to reference material. I realize that if I took the time to properly learn transact I would never have a need to post in a forum. However as I've stated I'm not a db developer and I do not have time to become one. I am in a spot of trouble with a query and was hoping someone would be able to post the proper code. I generally only use these forums as a last resort when I cannot find or figure out the answer on my own. I read the article and though I feel I have a slightly better understanding of how joins work I do not feel any closer to having an answer.

I mean no disrespect jsmith and I do appreciate you taking the time to try and help. And please believe I'm not trying to have people do my work for me. If I am mis-using this forum by requesting a specific response than I apologise.

However the fact remains that I still don't have my query. I could just run two separate queries and use my native language to create the result, I was just hoping I could do it all in one query and let mssql do that processing.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 14:52:01

The link provided for you teaches you all that and more!

E 12°55'05.25"
N 56°04'39.16"

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 14:59:55

Perhaps for someone that has a firm understanding of databases. I do not. I read the article and now feel more confused than ever. My database knowledge is dubious at best. Is the purpose of this forum to simply be an index to reference material? Why not take it a step further and simply post the names of all the best t-sql books? Call me a dummy if you wish but after reading that article I don't feel like I have an answer to my problem since I'm having a hard time relating there examples to my situation.

I apologise for being slow on the uptake.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 15:06:23

Where do you want us to stop spoon-feeding you?

E 12°55'05.25"
N 56°04'39.16"

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 15:13:27

I've asked three questions (maybe four) on this forum since I signed up. Each time the answers posted have taught me a great deal and I've been very appreciative of the responses I've received. This is the first time I've asked a question and simply been given a link to reference material. I've said I still don't understand and now I'm being told I'm being spoon fed? I've asked for help and I guess your answer is for me to either help myself or go away. Which is fine. I've just used three separate queries and use asp to do the evaluation. It' bulky and not best practice, but as I've said I don't understand it and don't have time to properly teach myself how to do it.

Thanks for your 'help'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-13 : 15:22:07

OK, I will just give you some SQL then ....

this returns one row per order:

select order_ID, sum(Total) as OrderTotal,
sum(Additions) as OrderAdditions,
sum(Payments) as OrderPayments,
sum(Total + Additions - Payments) as Owing
from
(select order_ID, Total, 0 as Additions, 0 as Payments from tbl_Orders
union all
select order_ID, 0, Additions, 0 from tbl_OrdersAdditions
union all
select order_ID, 0, 0, PaymentAmount from tbl_Payments
) x
group by order_ID

.. and this just returns grand totals for you, which looks like it may be what you want based on your example, but it is hard to tell for sure:

select ~~order_ID,~~ sum(Total) as OrderTotal,
sum(Additions) as OrderAdditions,
sum(Payments) as OrderPayments,
sum(Total + Additions - Payments) as Owing
from
(select order_ID, Total, 0 as Additions, 0 as Payments from tbl_Orders
union all
select order_ID, 0, Additions, 0 from tbl_OrdersAdditions
union all
select order_ID, 0, 0, PaymentAmount from tbl_Payments
) x
~~group by order_ID~~

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 15:28:31

[code]SELECT o.Order_ID,
o.Total + COALESCE(a.Total, 0) - COALESCE(p.Payment, 0)
FROM (
SELECT Order_ID,
SUM(Total) AS Total
FROM tbl_Orders
GROUP BY Order_ID
) AS o
LEFT JOIN (
SELECT Order_ID,
SUM(Total) AS Total
FROM tbl_OrdersAdditions
GROUP BY Order_ID
) AS a ON a.Order_ID = o.Order_ID
LEFT JOIN (
SELECT Order_ID,
SUM(PaymentAmount) AS Payment
FROM tbl_Payments
GROUP BY Order_ID
) AS p ON p.Order_ID = o.Order_ID[/code]Or this[code]SELECT Order_ID,
SUM(Total) AS Totalt,
SUM(Payment, 0) AS Payment
FROM (
SELECT Order_ID,
Total,
0 AS Payments
FROM tbl_Orders

UNION ALL

SELECT Order_ID,
Total,
0
FROM tbl_OrdersAdditions
GROUP BY Order_ID

UNION ALL

SELECT Order_ID,
0,
PaymentAmount
FROM tbl_Payments
GROUP BY Order_ID
) AS p
GROUP BY Order_ID[/code]Until next time!

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 15:33:18

quote:

Sadly I'm just not a very good db developer.

quote:

However as I've stated I'm not a db developer and I do not have time to become one.

Try it the hard way and see if the result changes?

[Code]

SELECT t.OrderID,(Total+TotalAdditions) - Payments AS Owing

FROM (Select Order_ID,Sum(IsNull(Total,0)) as Total
FROM tbl_Orders
Group by Order_ID) t
LEFT JOIN (Select Order_ID,Sum(IsNull(Total,0)) as TotalAdditions
FROM tbl_OrdersAdditions
Group by Order_ID) f
ON t.Order_ID = f.Order_ID

LEFT JOIN (Select Order_ID, Sum(IsNull(PaymentAmount,0)) as Payments
FROM tbl_Payments
Group by Order_ID) p
ON t.Order_ID = p.Order_ID

WHERE t.Order_ID = 87750
[/code]

Poor planning on your part does not constitute an emergency on my part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-13 : 15:35:23

I'd go with the UNION -- any time you are "joining" multiple transactional tables, UNIONS are just so simple, clean and efficient. No worries about duplicates, missing rows in any tables, etc.

- Jeff
http://weblogs.sqlteam.com/JeffS

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 15:37:54

thanks to everyone for your help. I ended up using dataguru1971's code and it works brilliantly. I can see now where I went wrong with my code. Thanks so much I really appreciate it.

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 15:41:04

Maybe I should explain more of why I used dataguru's. As I've said I don't really have a good understanding of db's and was having trouble getting the "where order_ID = 87750" to work on the other solutions, so since his worked I went with it. It wasn't a matter of holding a grudge or anything.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 15:48:16

Thanks, but I would probably have used Peso's code and jsmith's suggestion. The have a "tad" more credibility. (kind of like the pacific ocean is a "tad" wet), but I appreciate the sentiment.

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 15:51:40

Dataguru's suggestion will fail miserably if there are no additions or no payments due to involving NULL into the math.
In my first suggestion I have taken care of that scenario.

And that's why the UNION ALL method suggested by both Jeff and me is the better approach.

E 12°55'05.25"
N 56°04'39.16"

mr.modus
Starting Member

27 Posts

Posted - 2007-11-13 : 15:58:53

thanks for everyone's reply. Peso, could you just tell me how I'd use your suggestion but only for Order_ID = 87750.
Sorry to make you spell it out for me but I'm a newbie in the worst way when it comes to db.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 16:07:06

quote:
Originally posted by Peso

Dataguru's suggestion will fail miserably if there are no additions or no payments due to involving NULL into the math.
In my first suggestion I have taken care of that scenario.

And that's why the UNION ALL method suggested by both Jeff and me is the better approach.

E 12°55'05.25"
N 56°04'39.16"

Some people have no sense of humor!

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 16:24:16

No offense but since mr.modus does not know much about SQL, he would have come back tomorrow with same problem. The remark was for his point of view.

Cheer up! Have a look at this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92585

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 16:39:00

Just playing along Peso..I know it was for his benefit.

Poor planning on your part does not constitute an emergency on my part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-13 : 16:42:29

To filter for a single order_ID, just add a WHERE clause before the GROUP BY:


select 
  order_ID, 
  sum(Total) as OrderTotal,
  sum(Additions) as OrderAdditions,
  sum(Payments) as OrderPayments,
  sum(Total + Additions - Payments) as Owing
from
(
  select order_ID, Total, 0 as Additions, 0 as Payments from tbl_Orders
  union all
  select order_ID, 0, Additions, 0 from tbl_OrdersAdditions
  union all
  select order_ID, 0, 0, PaymentAmount from tbl_Payments
) x
where 
  order_id = 1234
group by 
  order_ID

That's it ...

- Jeff
http://weblogs.sqlteam.com/JeffS

mr.modus
Starting Member

27 Posts

Posted - 2007-11-14 : 07:51:35

jsmith that works fantastic. Thank you so much everyone. I really do only try to post when I just can't figure something out. I am a web app developer working on a project where our db guy left for another job and my boss said "you can just do the db, you know queries..." I know some simple stuff and what I can glean from examples of code but a true understanding is something I don't have and am working on. These forums are a great help and I appreciate everyone who replies, good or bad.

Cheers!
Mike

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources