CODEWITHSUNDEEP
mysql
da1lbi3
da1lbi3

Reputation: 4519

Calculate the sum from different tables

I have two tables:

My receipt table

Partnumber | Receipt | Location

And an sales table:

Partnumber | Sales | Location

What im trying to do is to calculate the stock quantity per location and per part number. I have the query to do this for one table:

Here i calculate the receipt parts on that location per partnumber

SELECT Location, sum(Receipt) FROM receipt WHERE Partnumber = 100 GROUP BY Location

You need to do the same with the sales table. And on the end i want to calculate the sum from the receipt table per location - the sum from the sales table per location.

Data:

Partnumber | Receipt | Location

100          2         500
100          5         500
100          2         300
200          4         600
300          8         500


Partnumber | Sales | Location

100          1         500
300          4         500
100          1         300

And here is the output:

Partnumber | stock |  location
100          6        500
100          1        300
200          4        600
300          4        500

So count the receipt articles based on part number per location minus the count sales on part number per location. Is this possible with one query?

Upvotes: 4

Views: 126

Answers (2)

Ján Stibila
Ján Stibila

Reputation: 637

Union can be handy with this problem:

(SELECT Partnumber, Receipt as quantity, Location FROM receipt)
UNION
(SELECT Partnumber, (-Sales) as quantity, Location FROM sales)

this will return

PARTNUMBER | QUANTITY | LOCATION
    100    |       2  | 500
    100    |       5  | 500
    200    |       4  | 600
    300    |       8  | 500
    100    |      -1  | 500
    300    |      -4  | 500

Notice, how I put negative value for quantity from sales table.

Now with this, you can do what you need:

SELECT DISTINCT Partnumber, sum(quantity) as Stock, Location FROM
(
    (SELECT Partnumber, Receipt as quantity, Location FROM receipt)
    UNION
    (SELECT Partnumber, (-Sales) as quantity, Location FROM sales)
) as UnitedTable
GROUP BY Partnumber, Location;

Dont forget to group by Location AND Partnumber. Result:

PARTNUMBER | STOCK | LOCATION
      100  |    6  | 500
      200  |    4  | 600
      300  |    4  | 500

http://sqlfiddle.com/#!2/76193d/19

Upvotes: 0

RandomSeed
RandomSeed

Reputation: 29809

You already know how to calculate the total number of parts received:

SELECT Location, SUM(Receipt)
FROM receipt WHERE Partnumber = 100 GROUP BY Location ;

Calculating the total number of parts sold is similar:

SELECT Location, SUM(Sales)
FROM sales WHERE Partnumber = 100 GROUP BY Location ;

Now all you need is calculate the difference, and all it takes is combining the two above queries:

SELECT Location, (receipt_count - COALESCE(sales_count, 0)) AS current_stock
FROM (
    SELECT Location, SUM(Receipt) AS receipt_count
    FROM receipt WHERE Partnumber = 100 GROUP BY Location
) AS Receipt
LEFT JOIN
 (
    SELECT Location, SUM(Sales) AS sales_count
    FROM sales WHERE Partnumber = 100 GROUP BY Location
) AS Sales USING (Location)

Upvotes: 3

Related Questions