SELECT a.p_id,a.product,SUM(a.qty) as receive , COALESCE(b.sold,0) AS sold,
(SUM(a.qty) - COALESCE(b.sold,0)) AS stock FROM plus2_product_receive a
LEFT JOIN
(SELECT p_id,product,SUM(qty) AS sold FROM `plus2_invoice_dtl` GROUP BY p_id) b
ON a.p_id=b.p_id
GROUP BY a.p_id
p_id
product
receive
sold
stock
1
Mouse
10
0
10
2
Key Board
8
0
8
3
Moniter
14
2
12
4
CPU
16
6
10
5
Pen Drive
25
6
19
6
Operating System
8
3
5
7
Power Unit
8
6
2
5.Above list (report ) with products having stock more than 10 quantity.
SELECT p_id,product,receive,sold,stock FROM (
SELECT a.p_id,a.product,SUM(a.qty) AS receive , COALESCE(b.sold,0) AS sold,
(SUM(a.qty) - COALESCE(b.sold,0)) AS stock FROM plus2_product_receive a
LEFT JOIN
(SELECT p_id,product,SUM(qty) AS sold FROM `plus2_invoice_dtl` GROUP BY p_id) b
ON a.p_id=b.p_id
GROUP BY a.p_id
) AS t WHERE stock >10;
p_id
product
receive
sold
stock
3
Moniter
14
2
12
5
Pen Drive
25
6
19
6.Above List of products with selling price.
SELECT t2.p_id,t2.p_name,receive,sold,stock,t2.price FROM (
SELECT a.p_id,a.product,SUM(a.qty) AS receive , COALESCE(b.sold,0) AS sold,
(SUM(a.qty) - COALESCE(b.sold,0)) AS stock FROM plus2_product_receive a
LEFT JOIN
(SELECT p_id,product,SUM(qty) AS sold FROM `plus2_invoice_dtl` GROUP BY p_id) b
ON a.p_id=b.p_id
GROUP BY a.p_id) AS t1
LEFT JOIN
plus2_product as t2 on t1.p_id=t2.p_id
WHERE stock >10