SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 joins and summing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikko50
Starting Member

3 Posts

Posted - 04/24/2004 :  19:12:10  Show Profile  Reply with Quote
Hi there. I have a SQL statement below that is not calculating correctly. Let's say I ship 10 apples. The following sql statement for my Inventory report shows correctly. But when I enter that I receive 5 apples. The sql inventory report shows 10. It seem to be matching or even mutiplying quantities. Any suggestions as how I can corect this???

select main.equipmentid, sum(receive.quantity) as 'receivedsum', sum(shiplist.quantity) as 'shippedsum' , sum(loan.quantity) as 'loanedsum'
from main
left join receive on main.equipmentid = receive.equipmentid
left join shiplist on main.equipmentid = shiplist.equipmentid
left join loan on main.equipmentid = loan.equipmentid
group by main.equipmentid order by main.equipment asc

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 04/24/2004 :  23:53:57  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Is main.equipment a one-to-one match with equipmentid?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

nikko50
Starting Member

3 Posts

Posted - 04/25/2004 :  09:24:12  Show Profile  Reply with Quote
Hi Derrick:) Main.equipmentis has a one to many relationship. Table receive and table shiplist can have many instances of equipmentid. Thanks
SHaron
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/25/2004 :  10:59:58  Show Profile  Visit nr's Homepage  Reply with Quote
consider this

main = 1
receive = 1,5
receive = 1,7
shiplist = 1,3
shiplist = 1,2

your query wil be summing the entries twice due to the cartesian products of receive and shiplist


try
select main.equipmentid,
(select sum(receive.quantity) from receive where main.equipmentid = receive.equipmentid) as 'receivedsum',
(select sum(shiplist.quantity) shiplist where main.equipmentid = shiplist.equipmentid) as 'shippedsum' ,
(select sum(loan.quantity) from loan where main.equipmentid = loan.equipmentid) as 'loanedsum'
from main
order by main.equipment asc

another way

select main.equipmentid, sum(receive.quantity) as 'receivedsum', sum(shiplist.quantity) as 'shippedsum' , sum(loan.quantity) as 'loanedsum'
from main
left join (select equipmentid, quantity = sum(quantity) from receive group by equipmentid) receive
on main.equipmentid = receive.equipmentid
left join (select equipmentid, quantity = sum(quantity) from shiplist group by equipmentid) shiplist
on main.equipmentid = shiplist.equipmentid
left join (select equipmentid, quantity = sum(quantity) from loan group by equipmentid) loan
on main.equipmentid = loan.equipmentid
group by main.equipmentid order by main.equipment asc




==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/25/2004 11:03:15
Go to Top of Page

nikko50
Starting Member

3 Posts

Posted - 04/25/2004 :  13:42:13  Show Profile  Reply with Quote
Thanks so much. I will try this out and let you know how it works. Talk to you later.
Sharon
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000