| Author |
 Topic  |
|
|
nikko50
Starting Member
3 Posts |
 Posted - 04/24/2004 : 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
USA
4184 Posts |
Posted - 04/24/2004 : 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 - 04/25/2004 : 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
United Kingdom
12543 Posts |
Posted - 04/25/2004 : 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. |
Edited by - nr on 04/25/2004 11:03:15 |
|
|
|
nikko50
Starting Member
3 Posts |
Posted - 04/25/2004 : 13:42:13
|
Thanks so much. I will try this out and let you know how it works. Talk to you later.
Sharon
|
|
|
| |
Topic |
|
joins and summing
