MySQL: How do I SUM non duplicates values when doing multiples JOINS

Question

I try to SUM values from columns, from a query which contains some JOINS.

Example:

SELECT
    p.id AS product_id,
    SUM(out_details.out_details_quantity) AS stock_bought_last_month,
    SUM(order_details.order_quantity) AS stock_already_commanded
FROM product AS p 
INNER JOIN out_details ON out_details.product_id=p.id 
INNER JOIN order_details ON order_details.product_id=p.id 
WHERE p.id=9507
GROUP BY out_details.out_details_pk, order_details.id;

I get this result :

+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
|       9507 |                      22 |                      15 |
|       9507 |                      22 |                      10 |
|       9507 |                      10 |                      15 |
|       9507 |                      10 |                      10 |
|       9507 |                       5 |                      15 |
|       9507 |                       5 |                      10 |
+------------+-------------------------+-------------------------+

Now, I want to SUM the values, but of course there are duplicates. I also have to group by product_id :

SELECT 
  p.id AS product_id,
  SUM(out_details.out_details_quantity) AS stock_bought_last_month,
  SUM(order_details.order_quantity) AS stock_already_commanded
FROM product AS p 
INNER JOIN out_details ON out_details.product_id=p.id 
INNER JOIN order_details ON order_details.product_id=p.id 
WHERE p.id=9507
GROUP BY p.id;

Result :

+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
|       9507 |                      74 |                      75 |
+------------+-------------------------+-------------------------+

The result wanted is :

+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
|       9507 |                      37 |                      25 |
+------------+-------------------------+-------------------------+

How do I ignores duplicates? Of course, the count of lines can change!

I think you're going to have to do two subqueries. One which groups on product_id & stock_bought_last_month and the other which groups on product_id & stock_already_commanded. The value for each will divide the sum(stock) by the count(stock) and also use a where to filter out zero counts. — Greg Bogumil, Jul 27 '10 at 16:35
Does this answer your question? [sql sum data from multiple tables](https://stackoverflow.com/questions/2591390/sql-sum-data-from-multiple-tables) — philipxy, May 19 '20 at 04:08

score 7 · Accepted Answer · answered Jul 27 '10 at 16:43

Select P.Id
    , Coalesce(DetailTotals.Total,0) As stock_bought_last_month
    , Coalesce(OrderTotals.Total,0) As stock_already_commanded
From product As P
    Left Join   (
                Select O1.product_id, Sum(O1.out_details_quantity) As Total
                From out_details As O1
                Group By O1.product_id
                ) As DetailTotals
        On DetailTotals.product_id = P.id
    Left Join   (
                Select O2.product_id, Sum(O2.order_quantity) As Total
                From order_details As O2
                Group By O2.product_id
                ) As OrderTotals
        On OrderTotals.product_id = P.id
Where P.Id = 9507

This is it! I didn't know I could have subqueries in JOIN items. Thank you that was really helpfull! — frinux, Jul 28 '10 at 07:13

score 1 · Answer 2 · answered Jul 27 '10 at 17:09

Another approach:

SELECT
    p.product_id,
    p.stock_bought_last_month,
    SUM(order_details.order_quantity) AS stock_already_commanded
from
(SELECT
    product.id AS product_id,
    SUM(out_details.out_details_quantity) AS stock_bought_last_month,
FROM product 
INNER JOIN out_details ON out_details.product_id=product.id 
WHERE product.id=9507
group by product.id
) AS p 
INNER JOIN order_details ON order_details.product_id=p.product_id
group by p.product_id;

Strictly speaking, the group by clauses are unnecessary in this example, as there's only one product id - however, if more than one was being selected, they would be necessary.

MySQL: How do I SUM non duplicates values when doing multiples JOINS

2 Answers2

Linked