Display the Employee Name (Boss) and number of Employee (Subordinates) in SQL

Question

I have a table emp having foll data:

EmpID    EmpName   MgrID 

100     King        NULL
101     Smith       100
102     Shine       100
103     Racy        102

Now i want to Display the Employee Name (Boss) and number of Employee (Subordinates) something like this

BOSS       SUBORDINATES

---

BLAKE                 5

CLARK                 1

FORD                  1

JONES                 2

KING                  3

SCOTT                 1

Please guide how to go about querying this table in SQL Server 2008.

Attempted query:

select e.first_name as ename,m.first_name as mname from employees e,employees m where e.manager_id=m.employee_id

Solution 1

• Start by self-joining on EmpID=MgrID
• Group by MgrID and EmpName
• Select EmpName and count(*)

Translating this to SQL is mechanical:

SELECT b.EmpName, COUNT(*)
FROM Employee e
JOIN Employee b ON b.EmpID=e.MgrID
GROUP BY b.EmpID, b.EmpName

Solution 2

CREATE TABLE test (
   EmpID INT,
   EmpName VARCHAR(100),
   MgrID INT)

INSERT INTO test VALUES (100, 'King', NULL),
                        (101, 'Smith', 100),
                        (102, 'Shine', 100),
                        (103, 'Racy', 102)

SELECT t1.EmpName AS Boss, 
       COUNT(*) AS Subordinates
FROM test AS t1 INNER JOIN test AS t2 ON t1.EmpID = t2.MgrID
GROUP BY t1.EmpName

Comments

[techie]

Though i am not able to execute the query presently,ur logic of approach seems to be right.Appreciate d help.

[techie]

Can u please guide when do we actually use group by?

[Sergey Kalinichenko]

@techie You use group by when you need to "collapse" groups of multiple rows into one row per group (the technical term is "to aggregate"). A good (but not a 100% certain) indication of having to use group by is the presence of aggregate functions, such as count, min, max, and so on, in your selection list.

[techie]

in the above query cud v also have used dis GROUP BY e.MgrID, e.EmpName It should work fine right as its just another instance.

[Sergey Kalinichenko]

@techie If you group by e.EmpName, every employee would become his/her own group (assuming the names are unique, as in your example). All groups will have only one member, so all counts will become 1. You could group by e.MgrID instead of b.EmpID (a bug I just fixed in my last edit) since these two are equal because of the join criteria.

[techie]

So my output will ideally luk like dis right- EmpName MgrCount King 2 Smith,Shine and racy with 0

[Sergey Kalinichenko]

@techie Follow this link to sqlfiddle to see how the output of this query is going to look. The query is going to exclude non-managers. If you would like to include them back, see this modified query here.

[techie]

Thanks a ton man!!!I never knew about this interface online provision.Thanks a million :D