How to get total sale by each agent with company total sale in each row using MySQL join and group by?

Get a column with total sale by each agent and second column with total sale for company as well in each row

I have 3 tables, queries added below for table schemas and data. I want to get total sales by each agent, but for each row
I also need the total sales by company too.

Here you can find tables queries :

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');

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');

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);

I have written this query, but it’s not sufficient to get the required output.

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

I need this as output :

companyName  |  agentName | totalSale  | companySale
company 1    |  agent 1   |  25        | 48
company 1    |  agent 2   |  23        | 48
company 2    |  agent 3   |  15        | 29
company 2    |  agent 4   |  14        | 29

>Solution :

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;

fiddle