How to join three tables to get Sum

0
votes

I have three tables: Products, Purchase, Invoice

Product table:
Producct_no     Name
1                        A
2                        B
3                        C

Purchase table:
Purchase_no    Product_no    Qty
001                    1                     81
002                    2                     150
003                    3                     10

Invoice table:
Invoice_no    Product_no    Qty
001                    1                 20
002                    2                 10
003                    3                 10

I want to get each product's purchase quantity and invoice quantity, I used following query 

    SELECT PR.product_no, sum(P.qty),sum(I.qty)
    FROM products PR 
    LEFT  JOIN invoice I ON I.product_no=PR.product_no
    LEFT  JOIN purchase P ON P.product_no=PR.product_no
    group by PR.product_no


product_no    sum(P.qty)    sum(I.qty)
001                    162             160
002                    150             50
003                    10               10

EDIT: Expected results
product_no    sum(P.qty)    sum(I.qty)
001                    81             20
002                    150             10
003                    10               10

My query is giving me wrong response (sum of quantities are wrong), please help me to correct my query to get the results properly. thanks

3
mysqlsql
What would be your expected result ? – Abhik Chakraborty
Leave out the sum and the group by and see what results are returned so you know what data you are summing. – Toby Allen
looks like a 1 to 1 to 1 mapping anyway. grouping seems redundant – pala_
There appear to be several typos in this, it is not clear what you mean. In your results table you have sum(P.qty) twice. Also It's clear what you want; each product only exists once on each table, so it is not clear why you want the sum, rather than the result? – nomistic
@pala_ I don't think it is a 1:1 mapping in reality. There could be other invoices and purchases with the same products, the example just shows a 1:1 mapping. I suspect that the data shown above is incomplete. Also, that this is the wrong way to do this query because of a fan-out if either left join returns more than one row (which I suspect it is in reality). – woot

3 Answers

1
votes

I think you have a problem with GROUP BY there. I would do something like this in this case

SELECT P.Product_no, Inv.InvProdSum, Pur.PurProdSum
FROM Product P
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM Invoice 
          GROUP BY Product_no) AS Inv
ON P.Product_no = Inv.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM Purchase 
            GROUP BY Product_no) AS Pur
ON P.Product_no = Pur.Product_no

Here is SQL FIddle for that http://sqlfiddle.com/#!9/731a5/1

NOTE i add some extra value here to test how it's work...

GL!

1
votes

I don't think your sample data is really what you have based on the information provided. My best guess here is that your query is doing a fan-out on either or both of those joins which is messing up your sums. You need to sum them separately, else additional rows in either on of those joins will fan out the other join, duplicating your results in the sum. This is evident in your result since 001 looks to be double (even though your sample data doesn't show it).

Something like this would ensure sums independent of each other:

SELECT PR.product_no, 
    ( SELECT sum(I.qty)
      FROM invoice I 
      WHERE I.product_no=PR.product_no ) invoice_qty,
    ( SELECT sum(P.qty)
      FROM purchase P 
      WHERE P.product_no=PR.product_no ) purchase_qty
FROM products PR
0
votes

Here is my solution without subqueries:

with 
  product(product_no, name) as (values
  (1, 'A'),
  (2, 'B'),
  (3, 'C')
),
  purchase(purchase_no, product_no, qty) as (values
  ('001', 1, 81),
  ('002', 2, 150),
  ('003', 3, 10),
  ('004', 1, 1000)
),
  invoice(invoice_no, product_no, qty) as (values
  ('001', 1, 20),
  ('002', 2, 10),
  ('003', 3, 10),
  ('004', 2, 5000)
),
  mixed as
(select d.product_no,
  purchase_no, p.qty as qty_purchase, 
  invoice_no, i.qty as qty_invoice,
  row_number() over(partition by purchase_no) as rn_purchase,
  row_number() over(partition by invoice_no) as rn_invoice
from product d
left join purchase p on p.product_no = d.product_no 
left join invoice i on i.product_no = d.product_no
)
select product_no,
  sum((case when rn_purchase = 1 then 1 else 0 end) * qty_purchase) as sum_purchase,
  sum((case when rn_invoice = 1 then 1 else 0 end) * qty_invoice) as sum_invoice
from mixed 
group by product_no
order by product_no
;

And results:

product_no|sum_purchase|sum_invoice|
----------|------------|-----------|
         1|        1081|         20|
         2|         150|       5010|
         3|          10|         10|