Abhishek February 2016

How to do nested sum in group by in optimized/one query in MYSQL?

I've 3 tables, Manager, Employee & Salary. Following is the structure of all the tables.

Manager

id  | Name
---------
111 | AAA
222 | BBB

Employee

id  | Name | Manager_id | new_policy_deductions
----------------------------------
1   | A B  | 111        | 100
2   | A C  | 111        | 200
3   | C D  | 222        | 200

Salary

id  | employee_id | Month | Emp_Salary | Manager_id
---------------------------------------------------
1   | 1           | Jan   | 500        | 111
2   | 1           | Feb   | 500        | 111
3   | 1           | Mar   | 600        | 111
4   | 2           | Apr   | 500        | 111
5   | 1           | Apr   | 700        | 111
6   | 3           | Mar   | 300        | 222
7   | 3           | Apr   | 500        | 222

employee_id is foreign key from Employee table to Salary table & manager_id is foreign key from Manager table to other tables.

Now, I need to construct a query such that I get following result.

Manager_id | Net_Salary
-----------------------
111        | 2500
222        | 600

How did I reached that numbers?

Take sum of salaries of all the employees under one manager (500 + 500 + 600 + 500 + 700 = 2800) & then subtract all new_policy_deductions in that manager (100 + 200 = 300). It implies 111 will have 2500 (2800 - 300). Similarly, for 222 we have 600.

I was able to achieve this using 2 queries, which are as follows,

x = select manager_id, sum(Emp_Salary) from Salary group by manager_id
y = select manager_id, sum(new_policy_deductions) from Employee group by manager_id

result = x - y

Can this be achieved in single SQL query? I

Answers


silverpenguin February 2016

select e.manager_id, (sum(e.Emp_Salary)-sum(s.new_policy_deductions)) 
FROM Salary as s
LEFT JOIN Employee as e
ON s.manager_id=e.manager_id
group by e.manager_id

would something like this be what you are looking for? might need some editing (typos are possible I dont have you db to check across)

In question to the comment this might be something you are also interested in:

select e.manager_id, (sum(e.Emp_Salary)-sum(s.new_policy_deductions)) 
FROM Salary as s
LEFT JOIN Employee as e
ON e.id=s.employee_id
group by e.manager_id

This is the best I can think of at current with the tables shown


Giorgos Betsos February 2016

Try this:

SELECT t1.Manager_id, sumOfSalaries - sumOfDeductions 
FROM (
  SELECT Manager_id, SUM(Emp_Salary) AS sumOfSalaries
  FROM Salary 
  GROUP BY Manager_id) AS t1
INNER JOIN (
  SELECT Manager_id, SUM(new_policy_deductions) AS sumOfDeductions
  FROM Employee
  GROUP BY Manager_id
) AS t2 ON t1.Manager_id = t2.Manager_id

Edit:

SELECT t1.Id, t1.Name, 
       COALESCE(sumOfSalaries, 0) - COALESCE(sumOfDeductions, 0) AS Net_Salary
FROM Manager AS t1
LEFT JOIN (
  SELECT Manager_id, SUM(Emp_Salary) AS sumOfSalaries
      FROM Salary 
      GROUP BY Manager_id
) AS t2 ON t1.Id = t2.Manager_id
  INNER JOIN (
      SELECT Manager_id, SUM(new_policy_deductions) AS sumOfDeductions
      FROM Employee
      GROUP BY Manager_id
) AS t3 ON t2.Manager_id = t3.Manager_id


Frayne Konok February 2016

I have a query that can help you: try this-

SELECT e.`Manager_id` as manager_id, (SELECT sum(s.`Emp_Salary`)
   FROM salary as s
   WHERE s.`Manager_id` = e.`Manager_id`) - sum(e.new_policy_deductions) as net_salary
FROM employee as e GROUP BY e.`Manager_id`

This is tested locally and output like you want. if some properties like-table name, field name change then please change. i think table names are are small later at my case.

Post Status

Asked in February 2016
Viewed 1,688 times
Voted 8
Answered 3 times

Search




Leave an answer