CareerCup

SQLServer:

WITH T1(Emp,Manager,Salary) AS
(
SELECT tt2.[Emp],tt2.[Manager],tt2.[Salary]
FROM [YourTable] AS tt1
RIGHT OUTER JOIN [YourTable] AS tt2 ON tt1.[Emp]=tt2.[Manager]
WHERE tt1.[Emp] is NULL
UNION ALL
SELECT r.[Emp],T1.[Manager],r.[Salary]
FROM [YourTable] AS r
INNER JOIN T1 ON r.[Manager]=T1.[Emp]
)
SELECT [Manager],SUM([Salary]) AS Salary
FROM T1
GROUP BY [Manager]
ORDER BY SUM([Salary]) DESC

SELECT S1.MANG,SUM(S1.SALARY) FROM SALARY S1 LEFT JOIN SALARY S2 ON
S1.MANG = S2.EMP WHERE S2.EMP IS NULL
GROUP BY S1.MANG

It can be done using Hierarchical queries in Sql. Use Connet by Prior clause. I will post the query soon.

Use union-find data structure to solve this problem.

get_parent(manager){
    if parent[manager] != manager
        m = get_parent(manager)
        parent[manager] = m
        return m
    else
        return manager
}
        
get_total(){
    for e in (Emp and Manager):
        parent[e] =  e
        total[e] = 0


    for  each (Emp Manager Salary ) record:{
        p = get_parent(Manager)
       parent[Emp] = p
       total[p] += Salary
    }
    for p in parent:
        if parent[p]  <> p:
            print p, total[p]
}

Thanks EchoJi, a very good illustration of recursive CTE.

The query will be looking something like this

SELECT TOP_LVL, SUM(SAL) FROM (SELECT CONNECT_BY_ROOT MNGR AS TOP_LVL, SAL FROM EMP CONNECT BY PRIOR EMP =  MNGR) GROUP BY TOP_LVL

SELECT E2.ENAME,
(SELECT SUM(e1.SAL)
FROM EMP1 E1
START WITH E1.EMPNO = e2.empno
CONNECT BY PRIOR e1.empno = e1.mgr
)sum_sal
FROM EMP1 E2
WHERE LEVEL =1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO =MGR;

use connect by prior

SELECT top_lvl, SUM(sal)
FROM (SELECT connect_by_root mngr AS top_lvl, sal
FROM emp
CONNECT BY PRIOR emp = mngr)
GROUP BY top_lvl

create table t1(
emp int,
mang int,
sal int)

insert into t1 values (1,null,100)
insert into t1 values (2,1,90)
insert into t1 values (3,1,90)
insert into t1 values (4,2,80)
insert into t1 values (5,3,80)
insert into t1 values (6,2,80)
insert into t1 values (7,3,80)

insert into t1 values (8,4,70)
insert into t1 values (9,5,70)
insert into t1 values (10,6,70)
insert into t1 values (11,7,70)

with temp1(emp,mang,sal,level) as (
select a.emp,a.mang,a.sal,1
from t1 a
where a.mang is null

union all

select a.emp,a.mang,a.sal,level+1
from t1 a
join temp1 b on a.mang = b.emp

)

select distinct level,(select sum(sal) from temp1 b where a.level <= b.level) from temp1 a
where level in (1,2)

SQL> select Top_Lvl, Sum(S)
2 from
3 (select connect_by_root M as Top_Lvl, S
4 from empl
5 connect by prior E=M)
6 where Top_Lvl Not In ('A', 'B')
7 group by Top_Lvl;

T SUM(S)
- ----------
T 26
F 13

;with t1 (employeeID, manager_EID, salary) as(
select e1.employeeID, e1.employeeID, e1.salary
from employee_manager_tbl e1
where e1.manager_EID is null
UNION ALL
select e3.employeeID, t1.manager_EID, e3.salary
from employee_manager_tbl e3 inner join t1
on e3.manager_EID=t1.employeeID)
select manager_EID, SUM(salary)
from t1
group by manager_EID
order by manager_EID

SELECT S1.MANG,SUM(S1.SALARY) FROM SALARY S1 LEFT JOIN SALARY S2 ON
S1.MANG = S2.EMP WHERE S2.EMP IS NULL
GROUP BY S1.MANG

Select m.eid,sum(sal) from emp m, emp e on(e.mid = m.eid) group by m.eid order by sum(sal) desc;

Select m.eid,sum(sal) from emp m, emp e on(e.mid = m.eid) group by m.eid order by sum(sal) desc;

SELECT *
FROM employee_h e
start with manager = 'T'
CONNECT BY PRIOR emp = manager;

SELECT *
FROM employee_h e
start with manager = 'T'
CONNECT BY PRIOR emp = manager;

SELECT *
      FROM employee_h e
      start with manager = 'T'
CONNECT BY  PRIOR emp = manager;

with recursive avgsalary as ( select employeeid, managerid, salary from salary where managerid='T' union select s.employeeid, s.managerid, s.salary from salary s inner join avgsalary a on s.managerid=a.employeeid ) select sum(salary) from avgsalary;