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
2
Answers
Here is the solution, you have to do two different group-by in the same query then you can use subquery [I have PostgreSQL so make changes accordingly to support your database if any]
fiddle