How to find the differences between the values of a specific column based on a key?

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;

also change your float columns to decimal. float is subject to rounding errors.

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
LEFT JOIN
          (SELECT product_id AS pid, COALESCE(sum(quantity), 0) amount 
           FROM invoice_details GROUP BY product_id) b 
          ON a.product_id = b.product_id;

If you do a left join between the first and second tables that b.amount will come back as NULL. I believe (but not certain) that it will be treated as zero in you expression.

thats working fine but it returns only the columns where the id’s of two tables match.

For example , i have two product_ids 7,8 in purchase_details table and have entries in invoice_details for only the product_id 7…

So it returns the stock for only product_id 7. i want to get stock for every product…

Replace the last Group clause with a where clause to tie the two tables you created together,

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 
WHERE a.product_id = b.product_id;