Sum up 2 tables?

Creating tables and queries

Sum up 2 tables?

Postby MrEgg964 » Tue Mar 08, 2016 4:07 pm

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   Expand viewCollapse view
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
LibreOffice 4.2.4.2 on Ubuntu 12.04
MrEgg964
 
Posts: 12
Joined: Mon May 26, 2014 5:51 pm

Re: Sum up 2 tables?

Postby eremmel » Tue Mar 08, 2016 6:34 pm

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?
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Sum up 2 tables?

Postby MrEgg964 » Tue Mar 08, 2016 6:41 pm

I am using MySQL.

Every record from both table must be displayed.
LibreOffice 4.2.4.2 on Ubuntu 12.04
MrEgg964
 
Posts: 12
Joined: Mon May 26, 2014 5:51 pm

Re: Sum up 2 tables?

Postby eremmel » Tue Mar 08, 2016 7:59 pm

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   Expand viewCollapse view
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
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests