Sum up 2 tables?

[MrEgg964]

Hello,

I have 2 tables : Production and Delivery, each having ID INT, Product_ID INT, Date DATE, Quantity INT. Product_ID is a foreign key linking to the Product table.

For each table, I am able to have a total by month, by product like so (eg for table Delivery, the same being for table Production) :

Code: Select all

SELECT DATE_FORMAT(Delivery.Date, '%m-%Y') AS DATE, Product_ID, SUM(Delivery.Quantity) AS Quantity_OUT FROM Delivery WHERE Delivery.Date >= NOW() GROUP BY YEAR(Delivery.Date), MONTH(Delivery.Date), Product_ID ORDER BY Delivery.Date ASC;

This returns something like this:
+---------+------------+--------------+
| DATE | Product_ID | Quantity_OUT |
+---------+------------+--------------+
| 03-2016 | 1 | 100 |
| 03-2016 | 2 | 200 |
| 03-2016 | 9 | 1000 |
| 04-2016 | 1 | 500 |
| 04-2016 | 2 | 100 |
| 04-2016 | 9 | 2000 |


and I do the same with table Production, with SUM(Production.Quantity) AS Quantity_IN.

I would like to join those 2 tables and have (Quantity_IN - Quantity_OUT) AS Stock, where for each Product_ID Stock is an accumulated value (should I base this on another subquery?) to return something like this:
+-----------+---------------+-----------------+--------------------+---------+
| DATE | Product_ID | Quantity_IN | Quantity_OUT | Stock |
+-----------+---------------+-----------------+--------------------+---------+
| 03-2016 | 1 | 100 | 100 | 0 |
| 03-2016 | 2 | 100 | 200 | -100 |--------- |
| 03-2016 | 9 | 1000 | 500 | 500 |..........| Stock from 03-2016 is accumulated into 04-2016
| 04-2016 | 1 | 500 | 200 | 300 |...........|
| 04-2016 | 2 | 100 | 100 | -100 | <------- |
| 04-2016 | 9 | 2000 | 2000 | 500 |

For Product_ID 2:
+-----------+---------------+-----------------+--------------------+---------+
| DATE | Product_ID | Quantity_IN | Quantity_OUT | Stock |
+-----------+---------------+-----------------+--------------------+---------+
| 03-2016 | 2 | 100 | 200 | -100 |
| 04-2016 | 2 | 100 | 100 | -100 |
Stock for 04-2016 is -100, because it is Stock value of 03-2016 (-100) + Stock value of 04-2016 (0).

This table would then allow me to graph each Product_ID to get a visual idea of the future estimated inventory. But I don't know how to get this done. I need help on how to join those 2 tables properly and how to accumulate the Stock for each Product_ID. Any suggestions?

Thanks

[eremmel]

Multiple solutions exists. Easiest is to use a FULL OUTER JOIN, but it depends on the database you use. Alternative is a LEFT JOIN - UNION - RIGHT JOIN combination.

Can you tell us your database?

Did you consider what you like so see when data is available in one table and not in the other for a certain combination?

[MrEgg964]

I am using MySQL.

Every record from both table must be displayed.

[eremmel]

The idea about how to simulate a FULL JOIN can be find here (do not take the main-answer).
The query is just out of my head. It might work or you need to build it up piece by piece to remove tipos (Tip Use MySQL Workbench).

Code: Select all

SELECT P.DATE
        , P.Product_ID
        , P.Quantity_OUT
        , COALESCE(Q.Quantity_IN, 0) AS Quantity_IN
    FROM 
      ( SELECT DATE_FORMAT(d.Date, '%m-%Y') AS DATE
            , d.Product_ID
            , SUM(d.Quantity) AS Quantity_OUT
        FROM Delivery d 
        WHERE d.Date >= NOW() 
        GROUP BY YEAR(d.Date), MONTH(d.Date), d.Product_ID 
      ) P
    LEFT JOIN
      ( SELECT DATE_FORMAT(p.Date, '%m-%Y') AS DATE
            , p.Product_ID
            , SUM(p.Quantity) AS Quantity_IN
        FROM Production p 
        WHERE p.Date >= NOW() 
        GROUP BY YEAR(p.Date), MONTH(p.Date), p.Product_ID 
      ) Q
    ON P.DATE = Q.DATA
        AND P.Product_ID = Q.Product_ID

UNION ALL

SELECT Q.DATE
        , Q.Product_ID
        , COALESCE(P.Quantity_OUT, 0) AS Quantity_OUT
        , Q.Quantity_IN
    FROM 
      ( SELECT DATE_FORMAT(d.Date, '%m-%Y') AS DATE
            , d.Product_ID
            , SUM(d.Quantity) AS Quantity_OUT
        FROM Delivery d 
        WHERE d.Date >= NOW() 
        GROUP BY YEAR(d.Date), MONTH(d.Date), d.Product_ID 
      ) P
    RIGHT JOIN
      ( SELECT DATE_FORMAT(p.Date, '%m-%Y') AS DATE
            , p.Product_ID
            , SUM(p.Quantity) AS Quantity_IN
        FROM Production p 
        WHERE p.Date >= NOW() 
        GROUP BY YEAR(p.Date), MONTH(p.Date), p.Product_ID 
      ) Q
    ON P.DATE = Q.DATA
        AND P.Product_ID = Q.Product_ID
    WHERE P.Product_ID IS NULL

ORDER BY 2,1