CODEWITHSUNDEEP
sql
da1lbi3
da1lbi3

Reputation: 4519

count values from different tables together

I want to count the values from a table together minus the count values from another table. Per item/part

i have tried this:

SELECT SUM(receipt) AS "receipt"
FROM receipt WHERE item = '".$partnumber."'
group by item

and this works for one table, but how can i do this with 2 tables?

table receipt:

item | receipt
100    5
100    2
200    10

table sales

part | sold
100    2

output:

partnumber | stock
100          5

So i want to calculate the sum per item/part and at the and i want to do receipt-sold for the stock. How can i do this?

Upvotes: 1

Views: 43

Answers (1)

D Stanley
D Stanley

Reputation: 152644

Take the sum the receipts:

SELECT item, SUM(receipt) receipt
FROM Table1 t1
GROUP BY item

then the sum of sales:

SELECT part, SUM(sold) sold
FROM Table2 t2
GROUP BY part

then join them together, taking the difference:

SELECT t1.item, (t1.receipt - COALESCE(t2.sold,0)) AS stock
FROM
(
    SELECT item, SUM(receipt) AS receipt
    FROM Table1 t1
    GROUP BY item
) t1
LEFT JOIN
(
    SELECT part, SUM(sold) AS sold
    FROM Table2 t2
    GROUP BY part
) t2
ON t1.item = t2.part

Note that the COALESCE will make sales 0 if there are no sales for a given part.

Upvotes: 2

Related Questions