Hello everyone
I have some trouble in self join..i want to find out how many employees work for each manager.i want to print the name of manager too
this is what i wrote..and i get the error at emp1.name : not a group by expression.

select emp1.name,count(emp1.id) from
employee emp1,employee emp2
where emp1.id = emp2.managerId
group by emp2.managerId;

Thanks in advance

can u give a sample data for me.

Sure..
If I run this query ..i get the result but I want to add the name of manager too..
Data is
LASTNAME empID ManagerId
------------------------------ ------- --- ----------
abc 1 1
rachel 2 1
joey 3 1
Chen 4 3
bowir 5 4
Scott 6 2

SQL> select count(emp1.id),emp1.managerId
2 from employee emp1,employee emp2
3 where emp1.id= emp2.managerId
4 group by emp1.managerId

Thanks for the reply..

select field,count(managerid) from tablename group by managerid

i think youve the same result. pls fedback me if not.

yes..but i want to use self join..+ i want manager name too in that
thanks

why u want to selft join? where that manager name came from?

from employee table itself


LASTNAME empID ManagerId
------------------------------ ------- --- ----------
abc 1 1
rachel 2 1
joey 3 1
Chen 4 3
bowir 5 4
Scott 6 2

example: manager of rachel is abc;
manager of joey is abc
manager name of chen is joey; etc
if I write following query i get the result as

select count(emp.id),emp.managerid
from employee emp,employee mag
where emp.id = mag.id
group by emp.supervisor;


COUNT(EMP.ID) manager
------------- ----------
3 1
1 2
1 3
1 4

but if i add name in that select stmt ..i don'n get proper reults

i dont get ur problem because if I issue this query it succeded.

Select *,count(id) idcount from tablename group by id .

Something like this works fine for me as well.
If you add emp.name into your select statement, you will not get the proper results because you are grouping by manager. (You'll only get one employee name...)
Also as a side note, if you store a manager as managing themself, your count may not be returning exactly what you want...