Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: self join problem

    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

  2. #2
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    can u give a sample data for me.

  3. #3
    Join Date
    Oct 2003
    Posts
    7

    self join

    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..

  4. #4
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    select field,count(managerid) from tablename group by managerid

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

  5. #5
    Join Date
    Oct 2003
    Posts
    7
    yes..but i want to use self join..+ i want manager name too in that
    thanks

  6. #6
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    why u want to selft join? where that manager name came from?

  7. #7
    Join Date
    Oct 2003
    Posts
    7
    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

  8. #8
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    i dont get ur problem because if I issue this query it succeded.

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

  9. #9
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Something like this works fine for me as well.
    Code:
    SELECT man.name, count( emp.employeeid ) AS num_employees
    FROM employee man, employee emp
    WHERE man.employeeid = emp.managerid
    GROUP BY man.name
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •