I have the following table:

What is a query that will return span of control, or the number of people reporting to each manager, given that the same 4-digit numbers appear in both the EMPNO, as well as the MGR columns?

If possible, I'd like to know how to get the result using both a subquery and/or a self join.

I used the following query:

SELECT e.ename, m.ename
from emp e, emp m
WHERE e.empno = m.mgr 

It results in a table that lists the names of each employee that correspond to each manager. Now, I just need to count the results and return a single number of employees grouped by each manager. I've tired to nest the above query in a general query using the COUNT function, but can't get it to work. A sample output of what I'd like to see is as follows:

CodePudding user response：

The following query will give you the manager id and a count of how many report to them

  SELECT MGR as MID, COUNT(*) AS REPORT_COUNT
  FROM emp
  GROUP BY MGR 

Now we need to get the manager name with a join

  SELECT E.ENAME, REPORT_COUNT
  FROM (
    SELECT MGR as MID, COUNT(*) AS REPORT_COUNT
    FROM emp
    GROUP BY MGR 
  ) X
  JOIN emp E ON E.empno = x.MID