skip to Main Content

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


  1. 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]

    select c.companyName, a.agentName, 
    (select sum(saleAmount) from sales where agentId = a.id group by agentId) as totalSale,
    (select sum(saleAmount) from sales where companyId = c.id group by companyId) as companySale
    from companies c, agents a where a.companyId = c.id;
    
    Login or Signup to reply.
  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

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search