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
[code=sql]
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

[/code]