How to join three tables to get Sum

Question

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

Leave out the sum and the group by and see what results are returned so you know what data you are summing. — Toby Allen, Apr 26 '15 at 14:57
looks like a 1 to 1 to 1 mapping anyway. grouping seems redundant — pala_, Apr 26 '15 at 14:57
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, Apr 26 '15 at 14:58
@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, Apr 26 '15 at 15:18
@woot yes, yet another case where the helpful sample info is .. actually a hindrance. — pala_, Apr 26 '15 at 15:22
Please check the expected results, very sorry for delay and my messy question. — Azmath, Apr 26 '15 at 18:10

woot · Accepted Answer · 2015-04-26T16:53:55.373

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

score 1 · Answer 2 · answered Apr 26 '15 at 16:29

1

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!

answered Apr 26 '15 at 16:29

Aleksandar Miladinovic

1,017
2
8
10

This does not give me expected results, I used Group by because I have many product_no records in Invoice and Purchase table, I simplified the table for this question. Thanks – Azmath Apr 26 '15 at 18:13
@Azmath i don't get it i got exact the same result's you want to get when i test it... I understand you want to SUM all values with the same product_no in table Invoice and Purchase and show it in one table which my query exactly did... [here](http://sqlfiddle.com/#!9/00694/4)... Anyway good luck i hope you'll find your solution :) – Aleksandar Miladinovic Apr 26 '15 at 18:20
Your answer worked, it was my fault my question is messy but you solved it thanks very much. – Azmath Apr 26 '15 at 18:26
@Azmath yea i see you are find solution woot solve it :). Glad you did it, that's only important. – Aleksandar Miladinovic Apr 26 '15 at 18:29

score 0 · Answer 3 · answered Dec 31 '20 at 17:25

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|

How to join three tables to get Sum

3 Answers3

Linked