MySQL LEFT JOIN Stock Report from Purchases and Sales

This exercise combines received inventory with invoice detail rows to calculate received quantity, sold quantity and available stock. COALESCE() converts missing sales totals to zero before the stock balance is calculated.

There are three tables in total

plus2_product
p_id ,
p_name
price

plus2_product_receive
id
p_id,
product
price ( Purchase Price)
qty ( Quanity )
dt ( date of purchase)

plus2_invoice_dtl
dtl_id,
inv_id ( Invoice id)
p_id (product id)
product
qty ( quantity sold)
price( sale price)

LEFT JOIN ( Basic Query) Download SQL Dump of these three tables here.

Invoice generation by allowing products with minimum stock level

From the above data we will produce following reports.

Exercise on LEFT JOIN
List of products received with total quantity
Total of quantity sold and price against each product.
List of Invoice with Total Price and Quanity against each invoice
List of Sum of Prducts received, sum products sold, balance available stock against each product.
Above list (report ) with products having stock more than 10 quantity.
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, a.product

p_id	product	receive
1	Mouse	10
2	Key Board	8
3	Moniter	14
4	CPU	16
5	Pen Drive	25
6	Operating System	8
7	Power Unit	8

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, product

p_id	product	Quantity	Total_price
3	Moniter	2	20.45
4	CPU	6	76.35
5	Pen Drive	6	20.80
6	Operating System	3	10.23
7	Power Unit	6	12.36

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;

inv_id	Quantity_sold	Total_price
4	13	80.66
5	5	28.95
6	5	30.58

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

COALESCE() to handle Null value

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, product) b 
ON a.p_id=b.p_id
GROUP BY a.p_id, a.product

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, product) b  
ON a.p_id=b.p_id 
GROUP BY a.p_id, a.product
) 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, product) b 
 ON a.p_id=b.p_id
GROUP BY a.p_id, a.product) AS t1 
LEFT JOIN 
plus2_product AS t2 ON t1.p_id=t2.p_id 
WHERE stock >10

p_id	product	receive	sold	stock	price
3	Moniter	14	2	12	20.45
5	Pen Drive	25	6	19	8.50

LEFT JOIN ( Basic Query) Download SQL Dump of these three tables here.
Exercise I : Sales report from Product and sales tables .
SELECT Query LEFT JOIN using Multiple Tables
Exercise : Sales - Agent using table JOIN and Date functions
SQL commands

All LEFT JOIN examples · Sales report exercise · COALESCE() · GROUP BY · SUM()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: