How to use Mysql SUM with JOIN

Question

I have the following tables:

purchase_tbl

id | productId | purchaseQuantity 
---+-----------+-----------------
1  | 1         | 30
2  | 2         | 30
3  | 1         | 10
4  | 2         | 10

sale_tbl

id | productId | saleQuantity
---+-----------+-------------
1  | 1         | 10
2  | 2         | 10
3  | 1         | 10
4  | 2         | 10
5  | 1         | 10
6  | 2         | 10

I need to get the output as this one:

productId | totalPurchasedQuantity| totalSaleQuantity
----------+-----------------------+------------------
1         | 40                    | 30
2         | 40                    | 30

I'm using this query and how to get the desired result?

SELECT purchase_tbl.productId
    , SUM(purchase_tbl.purchaseQuantity) AS totalPurchaseQuantity
    , SUM(sale_tbl.saleQuantity) AS totalSaleQuantity
FROM purchase_tbl
JOIN sale_tbl
    ON purchase_tbl.productId = sale_tbl.productId
GROUP BY purchase_tbl.productId

Current output

productId | totalPurchaseQuantity | totalSaleQuantity
----------+-----------------------+------------------
1         | 120                   | 60
2         | 120                   | 60

@Ravi query is fetching two rows with totalPurchasedQuantity as 120 and totalSaleQuantity as 60 — user7600685
– user7600685, Commented Jan 25, 2018 at 20:43
I wouldn't be comfortable assuming every purchased product has a sales product. — P.Salmon
– P.Salmon, Commented Jan 25, 2018 at 20:58
@danihp Is a left join is enough? Because products not in the purchase table will not be available for sale. Products in sale_tbl always have an entry in purchase_tbl — user7600685
– user7600685, Commented Jan 26, 2018 at 7:43

Ravi · Accepted Answer · 2018-01-25 20:50:48Z

2

You better group then in separate query, as table have multiple records for each product, which getting cross product.

SELECT purchase.productId, totalPurchaseQuantity, totalSaleQuantity
FROM
(SELECT purchase_tbl.productId
    , SUM(purchase_tbl.purchaseQuantity) AS totalPurchaseQuantity
FROM purchase_tbl
GROUP BY purchase_tbl.productId) purchase
INNER JOIN
(SELECT sale_tbl.productId
    , SUM(sale_tbl.saleQuantity) AS totalSaleQuantity
FROM sale_tbl
GROUP BY sale_tbl.productId
) sale ON sale.productId= purchase.productId;

answered Jan 25, 2018 at 20:50

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Strawberry Over a year ago

FWIW, I'd do the aggregation once, in the super query

Ravi Over a year ago

@Strawberry But, we don't have unique value in each tables, which causing the wrong output

Strawberry Over a year ago

I can't see that that would matter

Ravi Over a year ago

@Strawberry hmm.. let me think. Just for side note, i hope, you might have noticed OP has written a sql query in his question (which is similar to what you are saying), but not getting correct output.

Strawberry Over a year ago

I disagree. It's very different

|

cdaiga · Accepted Answer · 2018-01-26 05:06:37Z

0

To obtain your expected result you have to do the aggregation on the individual table before joining them. Your query with be like:

SELECT A.productId, A.totalpurchaseQuantity, B.totalsaleQuantity
FROM
(SELECT productId, SUM(purchaseQuantity) 
   totalpurchaseQuantity FROM purchase_tbl 
    GROUP BY productId) A JOIN 
(SELECT productId, SUM(saleQuantity) 
   totalsaleQuantity FROM sale_tbl 
    GROUP BY productId) B ON
A.productId=B.productId;

edited Jan 26, 2018 at 5:06

answered Jan 25, 2018 at 20:51

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

2 Comments

user7600685 Over a year ago

thank you :) got the desired result after making a change. Changed id in the first subquery to productId

cdaiga Over a year ago

Ah, I corrected my answer, the idea remains the same.

Collectives™ on Stack Overflow

How to use Mysql SUM with JOIN

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related