CODEWITHSUNDEEP
sqloracle-database
user618313
user618313

Reputation:

How do I use count and self join

I'm practicing SQL for an upcoming job interview and need some help. A friend suggested that I create a table and go as far as learning joins. I've been using SQL fiddle, oracle 11g R2.

My table looks as follows 

--------------------------
| ID | Name | Manager_ID | 
--------------------------

Where the Manager_ID's that aren't 0 (these are managers) are employees that have a foreign key that relates to the primary key of certain managers.

I'm trying to find out how many employees report to a manager and display them as follows: 

--------------------
| Name | Employees | 
--------------------

This is what I have so far, I'm just not sure how to list their names on the left field. 

SELECT COUNT(b.manager_id) as "Manages"
FROM employees a, employees b
WHERE b.manager_id > 0 
AND a.id = b.manager_id
GROUP BY a.id  
/

Thanks for any help. Please keep it basic, I'm still a noob.

Upvotes: 1

Views: 12031

Answers (2)

invertedSpear
invertedSpear

Reputation: 11054

Your are pretty close here, you are already grouping by manager, you just need that manager's name in your select

SELECT  a.name, COUNT(b.manager_id) as "Employees"
FROM employees a, employees b
WHERE b.manager_id > 0 
AND a.id = b.manager_id
GROUP BY a.name, a.id

Upvotes: 2

AlainD
AlainD

Reputation: 6356

There is a mental trick: Imagine your 3 column table (id, employee, manager_id) is a 6 column table (employee.id, employee.name, employee.manager_id, manager.id, manager.name, manager.manager_id) in which the three last columns have been compacted in a single id-column.

In your case, it is the same table, but most often it is not, so it quite easy to replace mentally a column-id (the foreign key) by the set of columns of the referred table (with id=primary key).

Usually also the foreign table is a list of countries, or product-line, that get repeated on several rows of the main table. Just the same as the same manager_id appears on more that one row.

There is no problem if some rows of the foreign table are not used (not all employees are managers), but you'll get a fatal error if the main table id tries to refers to a non existing id in the foreign table (a manager must manager at least one employee). To indicate that an employee has no manager, you put a non-existing employee.manager_id, You choose 0, more conventionally you use NULL.

  • To list the full table of all employee having a manager (everyone but the CEO) you would query:

    SELECT * FROM employee empl JOIN employee mngr ON empl.manager_id = mngr.id

    or (the same)

    SELECT * FROM employee empl, employee mngr WHERE empl.manager_id = mngr.id

  • To list the full table of all employees including those with no manager (everyone)

    SELECT * FROM employee empl LEFT JOIN employee mngr ON empl.manager_id = mngr.id

Your question is to get get the list of managers with the number of managed employees. So, supposing you have no employee with id=0 :

    SELECT manager.name, COUNT(*)
    FROM   employee empl, employee mngr
    WHERE  empl.manager_id = mngr.id
    GROUP BY mngr.id

To include all employees, even those managing 0 employees, is left (left join) as an exercise.

Upvotes: 3

Related Questions