T-SQL CTE count ALL Leaves beneath a node

All CTE examples that I can find with respect to counting involve the classic 'how many direct reports' does a manager have. I have a subtle take on that which I cant figure out. Essenitally I want to see for each node in a hierarchy, how many others in TOTAL are beneath at ALL levels. So if I had 12 people in a company, the chap at the top has 11 beneath him, He may have 3 direct reports, the first may have 2, the second 4 and the third 3 etc etc. I just can't figure out how to get the s开发者_如何学运维um of all beneath a node, please help!

If it has to be CTE, this one should work (query based on AdventureWorks database). Select populating CTE recursively builds list of all managers above given EmployeeID. Final select counts all occurrences of given EmployeeID in AllManagers column.

WITH EmployeeList (EmployeeID, ManagerID, Level, AllManagers) AS
    (
        SELECT 
            EmployeeID, 
            ManagerID, 
            0 AS Level, 
            CAST(',' + CAST(EmployeeID AS varchar) + ',' AS varchar) AS AllManagers 
        FROM HumanResources.Employee 
        WHERE ManagerID IS NULL
        UNION ALL
        SELECT 
            HumanResources.Employee.EmployeeID, 
            HumanResources.Employee.ManagerID, 
            EmployeeList.level + 1 AS level, 
            CAST(AllManagers + CAST(HumanResources.Employee.EmployeeID AS varchar) + ',' AS varchar) AS AllManagers 
        FROM HumanResources.Employee inner join EmployeeList ON HumanResources.Employee.ManagerID = EmployeeList.employeeID
    )
    SELECT 
        EmployeeID, 
        ManagerID, 
        Level, 
        AllManagers, 
        --have to subtract 1 because AllManagers path includes employee own ID
        (SELECT COUNT(*) FROM EmployeeList T1 WHERE T1.allmanagers LIKE '%,' + CAST(EmployeeList.employeeid AS varchar) + ',%') - 1 AS Subordinates
    FROM EmployeeList 
    ORDER BY Level

You have a tree structure represented by a table with a self referencing foreign key column and you want to count the number of descendants of each node in the tree. You will have the answer to your problem by reading this article http://www.sqlmag.com/article/tsql3/calculate-the-sum-of-salaries-in-an-employee-tree- More particulary in the "Solution Based on Recursive CTEs" paragraph. The clever part is that you have your result without having to build a "Level" column (if you don't need it).