Sum function is multiplied by 4

Question

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

Your join is producing more rows that you expect. This generally means that something is off in your join conditions or you are joining along different dimensions. — Gordon Linoff, Jun 03 '14 at 21:59
While you're at it, use ANSI join syntax. http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Eric Hauenstein, Jun 03 '14 at 22:08
I am fairly new to SQL. Do you have any idea what I need to change in my joins? The data doesn't contain duplicates as I can query each value individually just fine. The problem comes when I am joining the three tables. — the_brianb, Jun 03 '14 at 22:08
@bbarrow - Here's the other big hint - every time you have the equivalent of `SUM(), SUM()` your answers will be off. If either table has multiple rows per the grouping condition, the other result will be multiplied by the number of "duplicated" rows. If you only have one row, you don't need `SUM(...)` at all. You need to pre-aggregate the summed amounts - see [this question/answer](http://stackoverflow.com/questions/12322682/obtaining-sums-from-multiple-tables) for an example of where to start. — Clockwork-Muse, Jun 03 '14 at 22:31

score 0 · Answer 1 · answered Jun 04 '14 at 18:51

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

Sum function is multiplied by 4

1 Answers1

Linked