List the “manager’s name” and the “number of employees” in that manager’s department

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

Answer

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.

Advertisement

Answer