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

i've 3 tables, manager, employee & salary. following structure of tables.

manager

id  | name --------- 111 | aaa 222 | bbb 

employee

id  | name | manager_id | new_policy_deductions ---------------------------------- 1   | b  | 111        | 100 2   | 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 foreign key employee table salary table & manager_id foreign key manager table other tables.

now, need construct query such following result.

manager_id | net_salary ----------------------- 111        | 2500 222        | 600 

how did reached numbers?

take sum of salaries of employees under 1 manager (500 + 500 + 600 + 500 + 700 = 2800) & subtract new_policy_deductions in manager (100 + 200 = 300). implies 111 have 2500 (2800 - 300). similarly, 222 have 600.

i able achieve using 2 queries, follows,

x = select manager_id, sum(emp_salary) salary group manager_id y = select manager_id, sum(new_policy_deductions) employee group manager_id  result = x - y 

can achieved in single sql query? if yes, how?

note:

• the actual table names different used here.
• i can't modify table structure. designed long time ago.
• nested sql query not allowed, equivalent 2 queries, , inefficient.

edit:

following queries, in creating dummy data.

create table manager (id int, name text);  create table employee (id int, name text, manager_id int, new_policy_deductions int);  create table salary(id int, employee_id int, emp_salary int, manager_id int);  select * manager; insert manager (`id`, `name`) values (111,'aaa'), (222,'bbb');  select * employee;  insert employee (`id`, `name`, `manager_id`, `new_policy_deductions`) values (1,'a b',111,100), (2,'a c b',111,200), (3,'c b',222,200);   select * salary; insert salary (`id`, `employee_id`, `month`, `emp_salary`, `manager_id`) values (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); 

i've ignored foreign key constraints in query, dummy data. actual tables have foreign key constraints.

try this:

select t1.manager_id, sumofsalaries - sumofdeductions  (   select manager_id, sum(emp_salary) sumofsalaries   salary    group manager_id) t1 inner join (   select manager_id, sum(new_policy_deductions) sumofdeductions   employee   group manager_id ) t2 on t1.manager_id = t2.manager_id 

edit:

select t1.id, t1.name,         coalesce(sumofsalaries, 0) - coalesce(sumofdeductions, 0) net_salary manager t1 left join (   select manager_id, sum(emp_salary) sumofsalaries       salary        group manager_id ) t2 on t1.id = t2.manager_id   inner join (       select manager_id, sum(new_policy_deductions) sumofdeductions       employee       group manager_id ) t3 on t2.manager_id = t3.manager_id