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

Answer 1

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;