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' )
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;
We could also use the query following, which is closer to your original query.
SELECT 'stock' as "item", SUM(quantity) as "value" FROM stock WHERE product_id='4' GROUP BY product_id UNION ALL SELECT 'sales', SUM(qty) FROM order_details WHERE product_id='4';
Answered By – Kendle
Answer Checked By – David Goodson (BugsFixing Volunteer)