SQL LINKING two tables sales and agent by LEFT JOIN with aggregate functions

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 against all the agents in last 4 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   WEEKOFYEAR(dt_sales)=WEEKOFYEAR(CURDATE())
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(3) NOT NULL AUTO_INCREMENT,
  `agent_id` int(3) NOT NULL,
  `dt_sales` date NOT NULL,
  `p_id` int(3) NOT NULL,
  `quantity` int(3) NOT NULL,
  `total` int(6) NOT NULL,
  PRIMARY KEY (`sales_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO plus2_sales VALUES (1, 4,'2024-12-10',5,1,561),
(2, 3,'2024-12-10',1,2,512),
(3, 3,'2024-12-10',2,4,406),
(4, 3,'2024-12-10',1,4,435),
(5, 1,'2024-12-09',8,4,437),
(6, 4,'2024-12-09',4,5,483),
(7, 3,'2024-12-09',3,1,575),
(8, 3,'2024-12-09',7,1,421),
(9, 3,'2024-12-08',7,4,514),
(10, 1,'2024-12-08',5,3,420),
(11, 4,'2024-12-08',1,5,568),
(12, 2,'2024-12-08',1,4,478),
(13, 4,'2024-12-07',5,4,555),
(14, 3,'2024-12-07',5,2,491),
(15, 3,'2024-12-07',7,2,414),
(16, 4,'2024-12-07',7,1,591),
(17, 2,'2024-12-06',6,4,588),
(18, 3,'2024-12-06',6,1,561),
(19, 4,'2024-12-06',3,2,462),
(20, 2,'2024-12-06',5,5,564),
(21, 1,'2024-12-05',8,3,475),
(22, 1,'2024-12-05',5,4,521),
(23, 1,'2024-12-05',3,1,595),
(24, 2,'2024-12-05',8,5,516),
(25, 3,'2024-12-04',8,1,432),
(26, 3,'2024-12-04',4,1,595),
(27, 3,'2024-12-04',4,3,515),
(28, 4,'2024-12-04',6,4,543),
(29, 1,'2024-12-03',7,2,489),
(30, 3,'2024-12-03',3,2,572),
(31, 2,'2024-12-03',4,1,422),
(32, 2,'2024-12-03',4,1,573),
(33, 3,'2024-12-02',2,5,569),
(34, 2,'2024-12-02',2,3,583),
(35, 4,'2024-12-02',7,1,422),
(36, 3,'2024-12-02',1,2,562),
(37, 2,'2024-12-01',5,5,415),
(38, 1,'2024-12-01',1,4,515),
(39, 1,'2024-12-01',8,2,406),
(40, 2,'2024-12-01',2,5,578);CREATE TABLE IF NOT EXISTS `plus2_agent` (
  `agent_id` int(3) NOT NULL AUTO_INCREMENT,
  `coms` float 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=latin1 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

Subscribe to our YouTube Channel here

Example : Sales to Agent LINKING TABLES

plus2_agent

plus2_sales

Subscribe