I’ve two tables as purchase_details and invoice_details
and i want to store the inventory/stock of each product from the data of these two tables.
structure of purchase_details.
'pid', 'int(10)'
'product_id', 'int(10)'
'quantity', 'float(8,2)'
'amount', 'float(12,2)'
'expiry_date', 'date'
structure of invoice_details.
'invoice_id', 'int(10) unsigned'
'product_id', 'int(10) unsigned'
'quantity', 'float(10,2)'
'price', 'float(12,2)'
i want to calculate the total quantity of remaining stock for every product i.e
(quantity of sum of products from purchase_details) - (quantity of sum of products from invoice_details)
The invoice_details may or may not have entries for any product_id, so if there is no data available for a particular product_id in invoice_details then the value present in the purchase_details should be listed.
Product_id is would be same for the two tables.
Product table consists of product data, and the structure is
'id', 'int(10) unsigned'
'cid', 'int(10) unsigned'
'name', 'varchar(90)'
'selling_price', 'float(10,2)'
'mrp', 'float(10,2)'
'reorder_level', 'bigint(20) unsigned'
the invoice_details may or may not have entries for every product_id.
how can i proceed?
I tried the following query but it returns wrong answer,.
select a.pid, a.amount - b.amount from
(select product_id as pid, COALESCE(sum(quantity),0) amount
from purchase_details group by product_id) a,
(select product_id as pid, COALESCE(sum(quantity), 0) amount
from invoice_details group by product_id) b
group by a.pid;