MySQL Sales and Agent Reporting with JOIN, GROUP BY and Dates

This reporting exercise links sales rows to sales agents, then combines JOINs with GROUP BY, SUM() and date filters to calculate daily totals and commissions.

The weekly example uses YEARWEEK(date, 3) so the year is part of the comparison; comparing week numbers alone can mix the same week number from different years.

plus2_agent

agent_id

Autoincrement ID field, Unique id of the user / agent

coms

float with 2 decimal places, Commission of the user / agent

name

varchar(25) Name of the user / agent

email

varchar(30) Email address of the user / agent

address

varchar(30) Address of the user / agent

plus2_sales

sales_id

Autoincrement ID field, Unique id of the sales

agent_id

integer , id of the user / agent for the sale

dt_sale

Date field, Date of the sale ( can take multiple values of same date )

p_id

integer , id of the product ( can be linked to product table , future )

quantity

integer, number of product sold in the sale

total

Integer , based on this value commission will be calculated for the user / agent

Download the SQL dump of tables with sample rows to execute these queries at the end of this page.

All sales in the order of Date ( starting from recent date )

SELECT * FROM plus2_sales ORDER BY dt_sales DESC

Some records are shown here ( not all )

sales_id	agent_id	dt_sales	p_id	quantity	total
1	2	2021-06-07	8	2	492
2	1	2021-06-07	7	4	408
3	4	2021-06-07	5	5	492
4	1	2021-06-07	1	4	598
5	1	2021-06-06	5	2	406

Total sales date wise using GROUP BY and in the ORDER of most recent date.

SELECT dt_sales,SUM(total) FROM `plus2_sales` 
 GROUP BY dt_sales ORDER BY dt_sales DESC

Last 5 days sales in the order of Date, we used LIMIT here.

SELECT dt_sales,SUM(total) FROM plus2_sales GROUP BY dt_sales
   ORDER BY dt_sales DESC LIMIT 0,5

dt_sales	SUM(total)
2021-06-07	1990
2021-06-06	2116
2021-06-05	2018
2021-06-04	1986
2021-06-03	1790

Daily sales based on agent_id, we used SUM() here.

SELECT dt_sales, agent_id,SUM(total) FROM `plus2_sales`
 GROUP BY dt_sales,agent_id   ORDER BY dt_sales DESC

Daily sales based on agent_id and display name of the agent.

SELECT dt_sales, a.agent_id, b.name,SUM(total) FROM plus2_sales a 
LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id  
GROUP BY dt_sales,agent_id   ORDER BY dt_sales DESC

Few records ( not all rows ) are shown here

dt_sales	agent_id	name	SUM(total)
2021-06-07	1	Alex	1006
2021-06-07	2	Ronn	492
2021-06-07	4	King	492
2021-06-06	1	Alex	406
2021-06-06	2	Ronn	590
2021-06-06	4	King	1120
2021-06-05	2	Ronn	581
2021-06-05	4	King	1437
2021-06-04	2	Ronn	523
2021-06-04	3	Ravi	480
2021-06-04	4	King	983

Daily sales and daily commission against each agent with name.

SELECT dt_sales, a.agent_id, b.name,SUM(total),
FORMAT(b.coms*SUM(total),2) AS commission  
FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id 
 GROUP BY dt_sales,agent_id   ORDER BY dt_sales DESC

Few records ( not all rows )

dt_sales	agent_id	name	SUM(total)	commission
2021-06-07	1	Alex	1006	120.72
2021-06-07	2	Ronn	492	54.12
2021-06-07	4	King	492	113.16
2021-06-06	1	Alex	406	48.72
2021-06-06	2	Ronn	590	64.90
2021-06-06	4	King	1120	257.60
2021-06-05	2	Ronn	581	63.91
2021-06-05	4	King	1437	330.51
2021-06-04	2	Ronn	523	57.53
2021-06-04	3	Ravi	480	57.60
2021-06-04	4	King	983	226.09
2021-06-03	1	Alex	1790	214.80
2021-06-02	3	Ravi	1707	204.84
2021-06-02	4	King	481	110.63

Total commission against all the agents ( without any date restrictions )

SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) AS commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id
 GROUP BY agent_id

agent_id	name	coms	SUM(total)	commission
1	Alex	0.12	6206	744.72
2	Ronn	0.11	3796	417.56
3	Ravi	0.12	4112	493.44
4	King	0.23	5969	1,372.87

Total commission for the current date and the previous four calendar days ( use a fresh copy of sample sql dump below ) , more about CURDATE() and Date calculations here.

SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) AS commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id
WHERE   dt_sales >= DATE_SUB(CURDATE(), INTERVAL 4 DAY)
GROUP BY agent_id

agent_id	name	coms	SUM(total)	commission
1	Alex	0.12	1412	169.44
2	Ronn	0.11	2186	240.46
3	Ravi	0.12	480	57.60
4	King	0.23	4032	927.36

Total Commission against all the agents in current month ( use a fresh copy of sample sql dump below )

SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) AS commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id
WHERE dt_sales BETWEEN DATE_FORMAT(CURDATE(),'%Y-%m-01') AND CURDATE()
GROUP BY agent_id

agent_id	name	coms	SUM(total)	commission
1	Alex	0.12	4131	495.72
2	Ronn	0.11	2778	305.58
3	Ravi	0.12	2187	262.44
4	King	0.23	5005	1,151.15

Above query for current year

SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) AS commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id
WHERE dt_sales BETWEEN DATE_FORMAT(CURDATE(),'%Y-01-01') AND CURDATE()
GROUP BY agent_id

agent_id	name	coms	SUM(total)	commission
1	Alex	0.12	6206	744.72
2	Ronn	0.11	3796	417.56
3	Ravi	0.12	4112	493.44
4	King	0.23	5969	1,372.87

We will be changing the WHERE condition part to get records in different ranges.
Present calendar week data using the above query we will get.

SELECT  a.agent_id, b.name, b.coms,SUM(total),
 FORMAT(b.coms*SUM(total),2) AS commission  
 FROM plus2_sales a LEFT JOIN plus2_agent b ON a.agent_id=b.agent_id
WHERE   YEARWEEK(dt_sales, 3) = YEARWEEK(CURDATE(), 3)
GROUP BY agent_id

agent_id	name	coms	SUM(total)	commission
1	Alex	0.12	1006	120.72
2	Ronn	0.11	492	54.12
4	King	0.23	492	113.16

The following SQL dump is created dynamically by using todays date and random numbers. While using queries having CURDATE() will return different results based on the date of run of the query. So always use a fresh copy of this SQL dump to check all these queries.

CREATE TABLE IF NOT EXISTS `plus2_sales` (
  `sales_id` INT NOT NULL AUTO_INCREMENT,
  `agent_id` INT NOT NULL,
  `dt_sales` date NOT NULL,
  `p_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `total` INT NOT NULL,
  PRIMARY KEY (`sales_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
INSERT INTO plus2_sales VALUES (1, 4,'2026-09-28',5,1,563),
(2, 2,'2026-09-28',2,3,445),
(3, 3,'2026-09-28',7,1,465),
(4, 1,'2026-09-28',6,2,411),
(5, 2,'2026-09-27',7,3,408),
(6, 4,'2026-09-27',7,4,581),
(7, 1,'2026-09-27',3,1,549),
(8, 1,'2026-09-27',6,2,427),
(9, 2,'2026-09-26',2,1,502),
(10, 3,'2026-09-26',3,5,492),
(11, 2,'2026-09-26',5,3,480),
(12, 3,'2026-09-26',8,2,407),
(13, 2,'2026-09-25',8,2,455),
(14, 3,'2026-09-25',5,2,423),
(15, 3,'2026-09-25',1,5,427),
(16, 2,'2026-09-25',2,3,590),
(17, 2,'2026-09-24',2,3,496),
(18, 2,'2026-09-24',8,1,433),
(19, 3,'2026-09-24',7,1,428),
(20, 1,'2026-09-24',3,2,466),
(21, 4,'2026-09-23',5,1,436),
(22, 4,'2026-09-23',8,4,582),
(23, 4,'2026-09-23',6,5,478),
(24, 3,'2026-09-23',3,5,420),
(25, 2,'2026-09-22',8,5,595),
(26, 3,'2026-09-22',6,4,599),
(27, 3,'2026-09-22',7,1,414),
(28, 3,'2026-09-22',3,2,497),
(29, 4,'2026-09-21',7,4,574),
(30, 3,'2026-09-21',6,3,582),
(31, 4,'2026-09-21',7,4,527),
(32, 2,'2026-09-21',3,5,515),
(33, 2,'2026-09-20',8,2,585),
(34, 4,'2026-09-20',5,3,568),
(35, 2,'2026-09-20',1,1,404),
(36, 3,'2026-09-20',2,4,458),
(37, 2,'2026-09-19',7,5,421),
(38, 1,'2026-09-19',6,4,449),
(39, 3,'2026-09-19',6,3,456),
(40, 4,'2026-09-19',6,5,403);CREATE TABLE IF NOT EXISTS `plus2_agent` (
  `agent_id` INT NOT NULL AUTO_INCREMENT,
  `coms` DECIMAL(5,4) NOT NULL,
  `name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `address` varchar(25) NOT NULL,
  PRIMARY KEY (`agent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `plus2_agent`
--

INSERT INTO `plus2_agent` (`agent_id`, `coms`, `name`, `email`, `address`) VALUES
(1, 0.12, 'Alex', 'alex@example.com', 'alex address'),
(2, 0.11, 'Ronn', 'ronn@exampl.com', 'ronn address'),
(3, 0.12, 'Ravi', 'ravi@example.com', 'ravi address'),
(4, 0.23, 'King', 'king@example.com', 'king address');

LEFT JOIN ( Basic Query) RIGHT JOIN INNER Join CROSS Join
SQL Union
LEFT JOIN using Multiple Tables Exercise on LEFT JOIN using Product & Sales Tables LINKING TABLES

All LEFT JOIN examples · GROUP BY · SUM() · Date & Time Functions · Sales report exercise

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:

Subscribe to our YouTube Channel here

MySQL Sales and Agent Reporting

plus2_agent

plus2_sales

Related reporting tutorials

Subhendu Mohapatra