CODEWITHSUNDEEP
sqlsql-serverrecursion
xSpekkio
xSpekkio

Reputation: 15

Get count of total employees under each manager (SQL Server)

I need to get, for each of the five people in this table, the total employees reporting to each one of them (either directly or indirectly).

CREATE TABLE employees
    ([employee] nvarchar(30), [manager] nvarchar(30))
;

INSERT INTO employees
    ([employee], [manager])
VALUES
    ('Robert', null),
    ('Julian', 'Robert'),
    ('Sophie', 'Robert'),
    ('Lucy', 'Julian'),
    ('John', 'Lucy')
;

The output I expect is:

employee subordinates
Robert 4
Julian 2
Lucy 1
Sophie 0
John 0

I have searched everywhere how to do this type of recursion/hierarchical query, and it seems it is very common to approach this kind of exercise to get the employee hierarchy only (which I managed to do already), but not the total count of employees under each manager.

The best I could get was the following, but it only gets the count for the top manager, while I need it for all employees in the company in a single table (from an answer to this post: SQL Query to get recursive count of employees under each manager).

WITH EmployeeCTE
     AS (SELECT e.employee, e.manager as topmanager
         FROM   employees e, employees m
         WHERE  e.manager = m.employee AND m.manager IS NULL
         UNION ALL
         SELECT e.employee, mgr.topmanager
         FROM   employees e, EmployeeCTE mgr
         WHERE  e.manager = mgr.employee)
SELECT topmanager, count(*)
FROM   EmployeeCTE AS u
GROUP BY topmanager;

Here is a Fiddle to this: http://sqlfiddle.com/#!18/380ba/2

Upvotes: 1

Views: 2493

Answers (1)

LukStorms
LukStorms

Reputation: 29677

The problem is with getting the zero subordinates for those without subordinates.

One way to get them is via a LEFT JOIN of all employees to the results of the recursive CTE.

WITH CTE_Employees AS (
  SELECT employee
  FROM employees
  GROUP BY employee
)
, RCTE_Managers AS (
  SELECT
    manager as base_manager
  , 1 as depth
  , employee
  , manager as direct_manager
  FROM employees
  WHERE manager IS NOT NULL
  
  UNION ALL
  
  SELECT
    cte.base_manager
  , cte.depth + 1
  , emp.employee
  , emp.manager
  FROM RCTE_Managers cte
  JOIN employees emp
    ON emp.manager = cte.employee
)
SELECT 
  emp.employee
, COUNT(DISTINCT mgr.employee) AS subordinates
FROM CTE_Employees emp
LEFT JOIN RCTE_Managers mgr 
  ON mgr.base_manager = emp.employee
GROUP BY emp.employee
ORDER BY COUNT(mgr.employee) DESC, emp.employee;
employee subordinates
Robert 4
Julian 2
Lucy 1
John 0
Sophie 0

Test on db<>fiddle here

Upvotes: 3

Related Questions