MySQL LEFT JOIN Sales Report Exercise

This exercise uses products, sales and customers to build practical sales reports with aggregation and LEFT JOIN. The queries also show the anti-join pattern for finding products or customers with no matching sales.

When aggregate queries select nonaggregated columns, group by the columns that define the row. This keeps the examples compatible with MySQL's default ONLY_FULL_GROUP_BY behavior.

There are three tables in total

customers
c_id ,
customer
( 8 records)

products
p_id,
product and price
( 8 records )

sales
sale_id,
c_id ( customer id ), p_id (product_id), qty ( quantity sold) ,store ( name )

LEFT JOIN ( Basic Query) You can download SQL Dump of these three tables here.
From the above data we will produce following reports.

Exercise on LEFT JOIN
List of products sold
List of quantity sold against each product.
List of quantity and total sales against each product
List of quantity sold against each product and against each store.
List of quantity sold against each Store with total turnover of the store.
List of products which are not sold
List of customers who have not purchased any product.

Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join

1.List of products sold

SELECT p_id, product FROM sales GROUP BY p_id, product ORDER BY product;

product	p_id
CPU	4
Monitor	3
RAM	2

2.List of quantity sold against each product.

SELECT p_id, product, SUM(qty) AS total_qty FROM sales GROUP BY p_id, product ORDER BY product;

product	p_id	sum(qty)
CPU	4	1
Monitor	3	12
RAM	2	7

3. List of quantity and total sales against each product

SELECT a.p_id, a.product, SUM(a.qty) AS total_qty,
       SUM(a.qty * b.price) AS total_sales
FROM sales AS a
LEFT JOIN products AS b ON a.p_id = b.p_id
GROUP BY a.p_id, a.product
ORDER BY a.product;

product	p_id	sum(qty)	sum(qty*price)
CPU	4	1	55
Monitor	3	12	900
RAM	2	7	630

4. List of quantity sold against each product and against each store.

SELECT product, store, SUM(qty) AS total_qty FROM sales GROUP BY product, store ORDER BY product, store;

product	store	sum(qty)
CPU	DEF	1
Monitor	ABC	10
Monitor	DEF	2
RAM	ABC	3
RAM	DEF	4

5.List of quantity sold against each Store with total turnover of the store.

SELECT a.store, SUM(a.qty) AS total_qty,
       SUM(b.price * a.qty) AS total_price
FROM sales AS a
LEFT JOIN products AS b ON a.p_id = b.p_id
GROUP BY a.store
ORDER BY a.store;

store	total_qty	total_price
ABC	13	1020
DEF	7	565

Using MIN() with JOIN and ROW_NUMBER() to select one deterministic row per store

WITH ranked_sales AS (
  SELECT s.store, s.sale_id, s.qty, p.product, p.price,
         ROW_NUMBER() OVER (
           PARTITION BY s.store
           ORDER BY s.qty, s.sale_id
         ) AS rn
  FROM sales AS s
  INNER JOIN products AS p ON p.p_id = s.p_id
)
SELECT store, qty AS min_qty, product, price * qty AS total_price
FROM ranked_sales
WHERE rn = 1
ORDER BY store;

store	min_qty	product	total_price
ABC	2	Monitor	150
DEF	1	CPU	55

Using MAX() with JOIN and deterministic ranking

WITH ranked_sales AS (
  SELECT s.store, s.sale_id, s.qty, p.product, p.price,
         ROW_NUMBER() OVER (
           PARTITION BY s.store
           ORDER BY s.qty DESC, s.sale_id
         ) AS rn
  FROM sales AS s
  INNER JOIN products AS p ON p.p_id = s.p_id
)
SELECT store, qty AS max_qty, product, price * qty AS total_price
FROM ranked_sales
WHERE rn = 1
ORDER BY store;

store	max_qty	product	total_price
ABC	3	Monitor	225
DEF	2	RAM	180

6. List of products which are not sold

SELECT a.product, a.p_id
FROM products AS a
LEFT JOIN sales AS b ON a.p_id = b.p_id
WHERE b.sale_id IS NULL
ORDER BY a.p_id;

product	p_id
Hard Disk	1
Keyboard	5
Mouse	6
Motherboard	7
Power supply	8

7. List of customers who have not purchased any product.

SELECT a.customer, a.c_id
FROM customers AS a
LEFT JOIN sales AS b ON a.c_id = b.c_id
WHERE b.sale_id IS NULL
ORDER BY a.c_id;

customer	c_id
King	5
Ronn	7
Jem	8
Tom	9

Master Relational Data with SQLite and Tkinter

Dive into the world of relational databases with this in-depth tutorial. Learn how to create and manage interconnected tables for products, customers, and sales using SQLite. Enhance your Python applications by leveraging Tkinter to design user-friendly interfaces and generate dynamic reports. A perfect guide for mastering data relationships and crafting interactive database-driven applications.

Learn More

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

All LEFT JOIN examples · Stock report exercise · Sales-agent reporting · 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: