sum result is being multiplied by number of rows

Question

I'm trying to get 2 sums from my tables, one is a table of ordered items and another is a table of received items. With my current query i'm getting a sum of 2014 which is from 106(the actual sum I want to get) multiplied by 19(the number of records)

select POD.PO_No, SUM(POD.Qty) as Qty, RRD.RR_No, SUM(RRD.QtyRcvd) as QtyReceived
from tbl_PODetail POD inner join tbl_RRDetail RRD on POD.PO_No = RRD.PO_Reference
where POD.PO_No = 'PO-000001'
group by POD.PO_No, RRD.RR_No

What causes this and how can I correct it?

Sample Table

PO No      | Item   | Qty
-------------------------
PO-0000001 | Item A | 5
PO-0000001 | Item B | 7
PO-0000001 | Item B | 3

RR No      | Item   | Qty | PO_Reference
----------------------------------------
RR-0000001 | Item A | 5   | PO-0000001
RR-0000001 | Item B | 7   | PO-0000001
RR-0000001 | Item B | 3   | PO-0000001

Expected Output:

PO No      | Qty | RR No      | Qty Rcvd
-----------------------------------------
PO-0000001 | 15  | RR-0000001 |  15

What I get

PO No      | Qty | RR No      | Qty Rcvd
-----------------------------------------
PO-0000001 | 45  | RR-0000001 |  45

Please share us sample data so we can help you regarding your problem. — Jacky Montevirgen, Mar 02 '17 at 05:40

score 1 · Accepted Answer · answered Mar 02 '17 at 05:49

You missed the POD.Item = RRD.Item on join, that's why you are getting multiplied records.

select POD.PO_No, SUM(POD.Qty) as Qty, RRD.RR_No, SUM(RRD.QtyRcvd) as QtyReceived
from tbl_PODetail POD 
inner join tbl_RRDetail RRD on POD.PO_No = RRD.PO_Reference and POD.Item = RRD.Item
where POD.PO_No = 'PO-000001'
group by POD.PO_No, RRD.RR_No

Tim Biegeleisen · Answer 2 · 2017-03-02T05:57:11.887

This answers reflects what my understanding of your problem is. It appears that you want to aggregate the tbl_PODetail by PO number and compute a sum as quantity. Then, you want to aggregate a second table tbl_RRDetail by two columns, and join the first aggregated result to that using only the PO number. If this be correct, then one approach would be to use two separate subqueries for the aggregation and join them together.

WITH cte1 AS (
    SELECT RR_No, PO_Reference, SUM(QtyRcvd) AS QtyReceived
    FROM tbl_RRDetail
    GROUP BY RR_No, PO_Reference
),
WITH cte2 AS (
    SELECT PO_No, SUM(Qty) AS Qty
    FROM tbl_PODetail
    GROUP BY PO_No
)
SELECT t2.PO_No, t2.Qty, t1.RR_No, t1.QtyReceived
FROM cte1 t1
INNER JOIN cte2 t2
    ON t1.PO_Reference = t2.PO_No
WHERE t1.PO_No = 'PO-000001'

score 0 · Answer 3 · answered Mar 02 '17 at 05:52

Try aggregating the values in subqueries and joining them afterwards

Select *
From (
  Select po_no, sum(qty) qty
  From tbl_PODetail
  Group by po_no
) p join (
  Select rr_no, po_reference, sum(qty) qty_rcvd
  From tbl_RRDetail
  Group by rr_no, po_reference
) r on p.po_no = r.po_reference
Where p.po_no = 'PO-0000001'

score 0 · Answer 4 · answered Mar 02 '17 at 05:55

Try this also. Hope this could help.

        select * into #table1   from (                 
select 'PO-0000001' as PO_No,'Item A' as Item,5 as QTY union
select 'PO-0000001' as PO_No,'Item B' as Item,7 as QTY union 
select 'PO-0000001' as PO_No,'Item C' as Item,3 as QTY
)t

select * into #table2 from (
select 'RR-0000001' as RR_No,'Item A' as Item,5 as QTY,'PO-0000001' as PO_Referrence union
select 'RR-0000001' as RR_No,'Item B' as Item,7 as QTY,'PO-0000001' as PO_Referrence union
select 'RR-0000001' as RR_No,'Item C' as Item,3 as QTY,'PO-0000001' as PO_Referrence 
)t

select t1.PO_No,sum(t2.qty) as 'Qty' ,t2.RR_no,sum(t1.qty) as 'QTY Rcvd'
from #table1 t1
inner join #table2 t2
on t1.PO_no=t2.PO_Referrence and t1.item=t2.item
group by t1.PO_No,t2.RR_no

sum result is being multiplied by number of rows

4 Answers4

Linked