Exercise II using SQL left join, product stock report

1. Exercise on LEFT JOIN
2. List of products received with total quantity
3. Total of quantity sold and price against each product.
4. List of Invoice with Total Price and Quanity against each invoice
5. List of Sum of Prducts received, sum products sold, balance available stock against each product.
6. Above list (report ) with products having stock more than 10 quantity.
7. Above List of products with selling price.

1.List of products received with total quantity

SELECT a.p_id,a.product,SUM(a.qty) as receive 
FROM plus2_product_receive a GROUP BY a.p_id

2.Total of quantity sold and price against each product.

SELECT p_id,product,SUM(qty)AS Quantity,
FORMAT(SUM(price),2) AS Total_price FROM `plus2_invoice_dtl` GROUP BY p_id

3. List of Invoice with Total Price and Quanity against each invoice

SELECT inv_id,SUM(qty)AS Quantity_sold,FORMAT(SUM(price),2) AS Total_price 
FROM `plus2_invoice_dtl` GROUP BY inv_id;

4. List of Sum of Prducts received, sum products sold, balance available stock against each product.

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

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;

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