CODEWITHSUNDEEP
sqloracle-database
user2407714
user2407714

Reputation: 45

I need to write a query to list all employees and the number of subordinates they have

Employees with no subordinates should be listed as having zero subordinates should also be displayed by this query. I can currently return all employees with subordinates, but I can't seem to display employees with zero subordinates.

Here is the code so far:

SELECT s.empno, s.ename, COUNT(*) as "Num_subordinates"
FROM emp e
JOIN emp s ON s.empno=e.super 
GROUP BY s.empno, s.ename;

Upvotes: 2

Views: 5391

Answers (3)

nad2000
nad2000

Reputation: 4965

A few solutions for you problem:

1) grouped auto-join:

SELECT s.empno, s.ename, COUNT(*) as "Num_subordinates"
FROM emp e RIGHT JOIN emp s ON s.empno=e.super 
GROUP BY s.empno, s.ename;

2) left join with aggregate subordinate counts:

SELECT s.*, c.num_subordinates as "Num_subordinates"
FROM emp s LEFT JOIN (SELECT super AS empno, COUNT(*) FROM emp GROUP BY super) AS c
  ON c.empno = s.empno;

or using CTE:

WITH c AS (SELECT super AS empno, COUNT(*) FROM emp GROUP BY super)
SELECT s.*, c.num_subordinates as "Num_subordinates"
FROM emp s LEFT JOIN c
  ON c.empno = s.empno;

3) using scalar sub-query:

SELECT s.*, (SELECT COUNT(*) FROM emp WHERE super=s.empno) AS "Num_subordinates"
FROM emp s;

Upvotes: 0

peterm
peterm

Reputation: 92845

UPDATED

Assuming that data is similar to the following

| EMPNO |     ENAME |  SUPER |
------------------------------
|     1 |  Manager1 | (null) |
|     2 | Employee1 |      1 |
|     3 | Employee2 |      1 |
|     4 | Employee3 |      1 |
|     5 |  Manager2 | (null) |
|     6 | Employee5 |      5 |
|     7 | Employee6 | (null) |

A version with a subquery

SELECT e.empno, 
       e.ename, 
       (SELECT COUNT(*) 
          FROM emp 
         WHERE super = e.empno) "Num_subordinates"
  FROM emp e;

A version with a JOIN. You have to use LEFT JOIN since INNER JOIN filters out necessary rows and as JW correctly pointed out you need to COUNT on s.empno rather then *.

SELECT e.empno,
       e.ename,
       COUNT(s.empno) "Num_subordinates"
  FROM emp e LEFT JOIN emp s
    ON s.super = e.empno
 GROUP BY e.empno, e.ename
 ORDER BY e.empno

Output for both queries

| EMPNO |     ENAME | NUM_SUBORDINATES |
----------------------------------------
|     1 |  Manager1 |                3 |
|     2 | Employee1 |                0 |
|     3 | Employee2 |                0 |
|     4 | Employee3 |                0 |
|     5 |  Manager2 |                1 |
|     6 | Employee5 |                0 |
|     7 | Employee6 |                0 |

SQLFiddle (for both queries)

Upvotes: 1

John Woo
John Woo

Reputation: 263933

The only missing in your statement is to use LEFT JOIN rather than INNER JOIN. You also need to specify the ID instead of * when counting in LEFT JOIN so you will not yield 1 on the COUNT() if the employee don't have subordinate.

SELECT e.empno, e.ename, COUNT(s.empno) as "Num_subordinates"
FROM   emp e
       LEFT JOIN emp s ON s.empno = e.super 
GROUP  BY e.empno, e.ename

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 4

Related Questions