MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `companies` (
  `id` int(11) NOT NULL,
  `companyName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `companies` (`id`, `companyName`) VALUES
(1, 'company 1'),
(2, 'company 2');
SELECT * FROM companies;
CREATE TABLE `agents` (
  `id` int(11) NOT NULL,
  `companyId` int(11) DEFAULT NULL,
  `agentName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `agents` (`id`, `companyId`, `agentName`) VALUES
(1, 1, 'agent 1'),
(2, 1, 'agent 2'),
(3, 2, 'agent 3'),
(4, 2, 'agent 4');
SELECT * FROM agents;
CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `agentId` int(11) DEFAULT NULL,
  `companyId` int(11) DEFAULT NULL,
  `saleAmount` double(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `sales` (`id`, `agentId`, `companyId`, `saleAmount`) VALUES
(1, 1, 1, 10.00),
(2, 1, 1, 15.00),
(3, 2, 1, 11.00),
(4, 2, 1, 12.00),
(5, 3, 2, 15.00),
(6, 4, 2, 14.00);
SELECT * FROM sales;

Records: 2  Duplicates: 0  Warnings: 0




id
companyName


1
company 1

2
company 2



Records: 4  Duplicates: 0  Warnings: 0




id
companyId
agentName


1
1
agent 1

2
1
agent 2

3
2
agent 3

4
2
agent 4



Records: 6  Duplicates: 0  Warnings: 0




id
agentId
companyId
saleAmount


1
1
1
10.00

2
1
1
15.00

3
2
1
11.00

4
2
1
12.00

5
3
2
15.00

6
4
2
14.00


SELECT *
FROM `sales`
LEFT JOIN agents ON agents.id = sales.agentId 
LEFT JOIN companies ON companies.id = agents.companyId;


id
agentId
companyId
saleAmount
id
companyId
agentName
id
companyName


1
1
1
10.00
1
1
agent 1
1
company 1

2
1
1
15.00
1
1
agent 1
1
company 1

3
2
1
11.00
2
1
agent 2
1
company 1

4
2
1
12.00
2
1
agent 2
1
company 1

5
3
2
15.00
3
2
agent 3
2
company 2

6
4
2
14.00
4
2
agent 4
2
company 2


SELECT companies.companyName, 
       agents.agentName,
       SUM(sales.saleAmount) AS totalSale,
       SUM(SUM(sales.saleAmount)) OVER (PARTITION BY companies.companyName) AS companySale
FROM `sales`
JOIN agents ON agents.id = sales.agentId 
JOIN companies ON companies.id = agents.companyId 
GROUP BY companies.companyName, agents.agentName;


companyName
agentName
totalSale
companySale


company 1
agent 1
25.00
48.00

company 1
agent 2
23.00
48.00

company 2
agent 3
15.00
29.00

company 2
agent 4
14.00
29.00

id	agentId	companyId	saleAmount	id	companyId	agentName	id	companyName
1	1	1	10.00	1	1	agent 1	1	company 1
2	1	1	15.00	1	1	agent 1	1	company 1
3	2	1	11.00	2	1	agent 2	1	company 1
4	2	1	12.00	2	1	agent 2	1	company 1
5	3	2	15.00	3	2	agent 3	2	company 2
6	4	2	14.00	4	2	agent 4	2	company 2

companyName	agentName	totalSale	companySale
company 1	agent 1	25.00	48.00
company 1	agent 2	23.00	48.00
company 2	agent 3	15.00	29.00
company 2	agent 4	14.00	29.00