How to get total stock left using sql

March 16, 2022

I try to get remaining stock using sql.whats wrong with me.

SELECT (SELECT SUM(quantity) as stock 
        FROM stock WHERE product_id='4' 
        GROUP BY product_id 
        - 
        SELECT SUM(qty) as sales 
        FROM order_details 
        WHERE product_id='4'
       )

>Solution :

I think that this is what you need.
Please provide table definitions and sample data and required output if it is not right.

SELECT
  product_id,
  SUM(st.quantity) as stock,
  SUM(od.qty) as sales
FROM
  stock st
LEFT JOIN order_details od
ON st.product_id = od.product_id
WHERE st.product_id = '4'
GROUP BY product_id;