joins and summing - 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 2000 Forums

SQL Server Development (2000)

joins and summing

Author

Topic

nikko50
Starting Member

3 Posts

Posted - 2004-04-24 : 19:12:10

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

4184 Posts

Posted - 2004-04-24 : 23:53:57

Is main.equipment a one-to-one match with equipmentid?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nikko50
Starting Member

3 Posts

Posted - 2004-04-25 : 09:24:12

Hi Derrick:) Main.equipmentis has a one to many relationship. Table receive and table shiplist can have many instances of equipmentid. Thanks
SHaron

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-25 : 10:59:58

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.

nikko50
Starting Member

3 Posts

Posted - 2004-04-25 : 13:42:13

Thanks so much. I will try this out and let you know how it works. Talk to you later.
Sharon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources