count the no of emp under GPM list that emp with mgrid

[suneethakanchana]

Hi

MY table contains

empid empname mgrid mgrname Department


1 suneetha 2 Rama MTS
2 Rama 5 Venkat PM
3 Kiran NULL NULL GPM
4 Hema 2 Rama NULL
5 Venkat 3 Kiran SPM
NULL NULL NULL NULL MTS



i want to find number of employees under GPM

count the number of emp under GPM list that emp with mgrid


Thank you

[srinit]

Can you tell me your requirement clearly..and post the code what you tried

cheers..//

[deepuv04]

Try the following

with cte as
(
    select empid, empname, mgrid, mgrname ,Department
    from Table_Name where empid = (select distinct empid from Table_Name where department= 'GPM' ) 
    union all
    select O.empid, O.empname, O.mgrid, O.mgrname, O.Department
    from Table_Name O, Cte C WHERE O.mgrid   = C.empid 
)
SELECT COUNT(empid) FROM CTE