Counting the hierarchy

Question

I have a table with a hierarchy:

CREATE TABLE mng 
(
    id INTEGER NOT NULL PRIMARY KEY, 
    name VARCHAR(50) NOT NULL,
    manager_id INTEGER
);

INSERT INTO mng (id, name, manager_id) VALUES (1, 'Lola', NULL);
INSERT INTO mng (id, name, manager_id) VALUES (2, 'Bella', NULL);
INSERT INTO mng (id, name, manager_id) VALUES (3, 'Lo', 1);
INSERT INTO mng (id, name, manager_id) VALUES (4, 'Ann', 2);
INSERT INTO mng (id, name, manager_id) VALUES (5, 'Ki', 3);
INSERT INTO mng (id, name, manager_id) VALUES (6, 'Qo', 5);

I need to print all top managers (where manager_id is NULL) with their subordinates count (including all levels)

   id   |   cnt
--------+---------
   1    |   3
   2    |   1

upd was trying something like this:

WITH DirectReports(ManagerID, Employee) AS   
(  
    SELECT id, 0 AS Employee  
    FROM mng 
    WHERE manager_id IS NULL  
  
    UNION ALL  
  
    SELECT e.id, Employee + 1  
    FROM mng AS e  
    INNER JOIN DirectReports AS d  
    ON e.manager_id  = d.ManagerID    
)  
SELECT ManagerID, Employee  
FROM DirectReports  
ORDER BY ManagerID;  

got level number, but how to get count?

Answer 1

The issue with your original code is you lose track of the "top manager". You can, however, retain this within the CTE, and simply have it listed for every employee:

WITH DirectReports(id, TopMgr, ManagerID, Employee) AS   
(  
    SELECT id, id, NULL, 0 AS Employee
    FROM mng 
    WHERE manager_id  IS NULL  
  
    UNION ALL  
  
    SELECT e.id, d.TopMgr, e.manager_id, 1 AS Employee
    FROM mng AS e  
    INNER JOIN DirectReports AS d  
    ON e.manager_id  = d.id   
)  
SELECT TopMgr, SUM(Employee)
FROM DirectReports  
GROUP BY TopMgr
ORDER BY TopMgr

Output:

    TopMgr  EmployeeCount
1   3
2   1