[SOLVED] How to get total stock left using sql

Issue

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;

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)