List the "manager's name" and the "number of employees" in that manager&#03-CodePudding

"List the "manager's name" and the "number of employees" in that manager's department" is the requested query for my class. I cannot embed pictures yet so I will do my best to have them formatted properly on here

Here is some data from the employees table:

EMPNO---ENAME----JOB----------MGR----SAL---- DEPTNO

7839--- KING-----PRESIDENT---     ---5000-----10

7698----BLAKE----MANAGER-----7839----2850-----30

7782----CLARK----MANAGER-----7839----2450-----10

7566----JONES----MANAGER-----7839----2975-----20

7654----MARTIN---SALESMAN----7698----1250-----30

7499----ALLEN----SALESMAN----7698----1600-----30

7900----TURNER---SALESMAN----7698----1500-----30

7521----JAMES----CLERK-------7698----950------30

7902----WARD-----SALESMAN----7698----1250-----30

7902----FORD-----ANAYLYST----7566----3000-----20

the code I currently have is:

SELECT A.ENAME, COUNT(*)

FROM EMP A

JOIN EMP B

ON A.DEPTNO = B.DEPTNO

GROUP BY A.ENAME;

This code seems to give me all employees and the number of employees in their department. From the question, I believe that I only need the 3 employees with the with job = 'MANAGER'. Unfortunely, I cannot figure out how to accomplish this and have the correct count of employees in their dept. Any direction or help would be appreciated.

CodePudding user response：

SELECT A.ENAME, COUNT(*) 
FROM EMP A 
   JOIN EMP B ON A.DEPTNO = B.DEPTNO 
WHERE A.JOB = 'MANAGER'
GROUP BY A.ENAME;

Please correct me if I'm wrong.

CodePudding user response：

That's the good, old Scott's EMP table. If presented as a tree, it looks like this:

SQL> select lpad(' ', 2 * level) || ename hierarchy
  2  from emp
  3  connect by prior empno = mgr
  4  start with mgr is null;

HIERARCHY
--------------------------------------------------------------------------------
  KING
    JONES
      SCOTT
        ADAMS
      FORD
        SMITH
    BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
    CLARK
      MILLER

14 rows selected.

SQL>

Apparently,

King is Jones', Blake's and Clark's manager
Jones is manager to Scott and Ford
Scott is manager to Adams
Ford is manager to Smith
etc.

Self-join then returns

SQL> select m.ename, count(*)
  2  from emp e join emp m on m.empno = e.mgr
  3  group by m.ename
  4  order by count(*) desc;

ENAME        COUNT(*)
---------- ----------
BLAKE               5
KING                3
JONES               2
SCOTT               1
FORD                1
CLARK               1

6 rows selected.

SQL>

On the other hand, if you want to specify MANAGERS only along with some data from the DEPT table, you'll again need self-join (of the EMP table), but this time it'll be OUTER JOIN because department "Operations" doesn't have any employees (nor a manager):

SQL> select d.dname, e.ename, count(e.rowid) cnt
  2  from dept d left join emp e on e.deptno = d.deptno
  3                             and e.job = 'MANAGER'
  4              left join emp m on e.deptno = m.deptno
  5  group by d.dname, e.ename;

DNAME          ENAME             CNT
-------------- ---------- ----------
RESEARCH       JONES               5
SALES          BLAKE               6
ACCOUNTING     CLARK               3
OPERATIONS                         0

SQL>

CodePudding user response：

You can use a hierarchical query (which will avoid having to self-join the table).

If you want all to count all the people in the department who do not have the job of MANAGER then:

SELECT MAX(PRIOR ename) AS mgr_name,
       COUNT(*) AS num_employees
FROM   emp
WHERE  LEVEL = 2
START WITH job = 'MANAGER'
CONNECT BY NOCYCLE
       PRIOR deptno =  deptno
AND    PRIOR empno  <> empno
GROUP BY PRIOR empno

Which outputs:

MGR_NAME NUM_EMPLOYEES

JONES 4

CLARK 2

BLAKE 5

MGR_NAME	NUM_EMPLOYEES
JONES	4
CLARK	2
BLAKE	5

If you want to count all the people who are in the same department as the manager and are managed by that manager then:

SELECT MAX(PRIOR ename) AS mgr_name,
       COUNT(*) AS num_employees
FROM   emp
WHERE  LEVEL = 2
START WITH job = 'MANAGER'
CONNECT BY PRIOR empno = mgr
GROUP   BY PRIOR empno

Which, for the Scott schema, outputs:

MGR_NAME NUM_EMPLOYEES

JONES 2

CLARK 1

BLAKE 5

MGR_NAME	NUM_EMPLOYEES
JONES	2
CLARK	1
BLAKE	5

If you wanted to do it without hierarchical queries then you can use:

SELECT MAX(m.ename) AS mgr_name,
       COUNT(*) AS num_employees
FROM   emp m
       INNER JOIN emp e
       ON (   m.deptno =  e.deptno
          AND m.empno  <> e.empno )
WHERE  m.job = 'MANAGER'
GROUP BY m.empno

and

SELECT MAX(m.ename) AS mgr_name,
       COUNT(*) AS num_employees
FROM   emp m
       INNER JOIN emp e
       ON (   m.deptno = e.deptno
          AND m.empno  = e.mgr )
WHERE  m.job = 'MANAGER'
GROUP BY m.empno

db<>fiddle here