Sum function is multiplied by 4

问题:

I have put in the following code to sum up the values in POLIN.Qtyremn and OELIN.Otyord. It almost works, but instead of just summing the values, it is multiplying the quantities by four and adding them. I think this is being caused by the joins and it is calling the value four times and then adding them.

SELECT POLIN.Itemkey, POLIN.Description, POLIN.Location, INLOC.Qtyonhand, Sum(POLIN.Qtyremn), Sum(OELIN.Qtyord)
FROM X.dbo.INLOC INLOC, X.dbo.OELIN OELIN, X.dbo.POLIN POLIN
WHERE INLOC.Itemkey = POLIN.Itemkey AND INLOC.Itemkey = OELIN.Itemkey AND INLOC.Location = POLIN.Location AND INLOC.Location = OELIN.Location AND ((POLIN.Location='SPL') AND (POLIN.Qtyremn>0))
GROUP BY POLIN.Itemkey, POLIN.Description, POLIN.Location, INLOC.Qtyonhand
ORDER BY POLIN.Itemkey

回答1:

I believe it's because you're not summing QtyOnHand -- If I undersatnd this correctly, this is a number... so you don't want to group by it... you want to sum it... see below.. this should resolve your issue of duplicates...

SELECT  POLIN.Itemkey, POLIN.Description, POLIN.Location, 
        sum(INLOC.Qtyonhand) [QtyOnHand], Sum(POLIN.Qtyremn) [QtyRemn], Sum(OELIN.Qtyord) [QtyOrd]
FROM X.dbo.INLOC INLOC, X.dbo.OELIN OELIN, X.dbo.POLIN POLIN
WHERE INLOC.Itemkey = POLIN.Itemkey AND INLOC.Itemkey = OELIN.Itemkey AND INLOC.Location = POLIN.Location AND INLOC.Location = OELIN.Location AND ((POLIN.Location='SPL') AND (POLIN.Qtyremn>0))
GROUP BY POLIN.Itemkey, POLIN.Description, POLIN.Location
ORDER BY POLIN.Itemkey