Help me with one query to calculate stock

[albertochiang]

Hi, I want to calculate my stock. the formula is :
end of stock = beginning of stock + purchase - sales
(I make it simple ... so I didn't include 'stock return' and 'stock opname' etc)

My stock table is :
prodid (varchar)
bos (double) -> beginning of stock

Purchase table is :
date (date)
invoiceID (varchar)
prodid (varchar)
qttybuy (double)

Sales table is :
date (date)
invoiceID (varchar)
prodid (varchar)
qttysales (double)

How to calculate my 'end of stock' using just one query. Please help me, thank you.

[NogDog]

Maybe...

SELECT stock.prodid, bos + SUM(qttybuy) - SUM(qttysales)
FROM stock LEFT JOIN purchase USING (prodid) LEFT JOIN sales USING(prodid)
GROUP BY stock.prodid

...?