How to count all descendant nodes in a tree?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Query Editor to demonstrate the enumeration of descendants (all staff reporting to manager directly/indirectly) of a tree node.

-- Count staff for each Manager reporting direcly or indirectly

USE AdventureWorks;

WITH OrgChart

  AS (SELECT Root.EmployeeName,

    Root.ManagerName,

    Root.EmployeeId,

    Root.ManagerId,

    CONVERT(VARCHAR(MAX),Root.PathSequence) AS PathLabel

   FROM (SELECT EmployeeName = c.FirstName + ' ' + c.LastName,

      ManagerName = convert(VARCHAR,''),

      e.EmployeeId,

      e.ManagerId,

      char(64 + ROW_NUMBER()

         OVER(ORDER BY e.EmployeeId)) AS PathSequence

     FROM HumanResources.Employee e

      INNER JOIN Person.Contact c

       ON e.ContactID = c.ContactID

     WHERE e.ManagerId IS NULL) Root -- Anchor/root term

   UNION ALL

   SELECT Branch.EmployeeName, -- Recursive term

    Branch.ManagerName,

    Branch.EmployeeId,

    Branch.ManagerId,

    PathLabel = Branch.PathLabel + CONVERT(VARCHAR(MAX),Branch.PathSequence)

   FROM (SELECT EmployeeName = c.FirstName + ' ' + c.LastName,

      ManagerName = convert(VARCHAR,cm.FirstName + ' ' + cm.LastName),

      e.EmployeeId,

      e.ManagerId,

      OrgChart.PathLabel,

      PathSequence = char(64 + ROW_NUMBER()

             OVER(ORDER BY e.EmployeeId))

     FROM OrgChart

      INNER JOIN HumanResources.Employee e

       ON e.ManagerId = OrgChart.EmployeeId

      INNER JOIN Person.Contact c

       ON e.ContactID = c.ContactID

      INNER JOIN HumanResources.Employee em

       ON em.EmployeeID = e.ManagerID

      INNER JOIN Person.Contact cm

       ON em.ContactID = cm.ContactID) Branch)

SELECT      A.EmployeeName,

            A.PathLabel,

            DescendantsCount=COUNT(*)-1

FROM  OrgChart A

LEFT JOIN OrgChart B

ON A.PathLabel = LEFT(B.PathLabel,len(A.PathLabel))

GROUP BY A.EmployeeName, A.PathLabel

HAVING COUNT(*)-1 > 0

ORDER BY DescendantsCount desc

/* EmployeeName   PathLabel   DescendantsCount

Ken Sánchez             A           289

James Hamilton          AE          208

Peter Krebs             AEA         184

Laura Norman            AD          28

Brian Welcker           AF          17

Terri Duffy             AB          13

Jack Richins            AEAS        13

Jo Brown                AEAC        12

Jinghao Liu             AEAF        12

Roberto Tamburello      ABA         12 ....*/