How to count hierarchical rows count with a CTE Suppose that I have two tables Region and Devices.  Region provides tree structure (by self referencing a ParentRegionId foreign key).  Every device belongs to one region (by the RegionId foreign key).

See this fiddle for a better understanding of the tables.

I need to count 'Region''s devices including nested ones similar to this:

+---------+------------+--------------+
| RegioId | RegionName | DevicesCount |
+---------+------------+--------------+
|       0 | Region0    |            5 |
|       1 | Region1    |            2 |
|       2 | Region2    |            2 |
|      11 | Region11   |            1 |
|      12 | Region12   |            0 |
|      21 | Region21   |            0 |
|      22 | Region22   |            1 |
+---------+------------+--------------+
Here's the trick you're after - use the CTE to build a list of each region, cross referenced with every region which counts towards it:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT [PID], [ID]
FROM cte
ORDER BY 1,2


And then let's use it as a cross-reference table to count devices:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT
  r.[ID] AS [RegioId],
  r.[Name] AS [RegionName],
  count(d.[ID]) AS [DevicesCount]
FROM
  [dbo].[Regions] r
  LEFT JOIN cte x ON r.[ID] = x.[PID]
  LEFT JOIN [dbo].[Devices] d ON x.[ID] = d.[RegionId]
GROUP BY 
  r.[ID],
  r.[Name]
ORDER BY r.[ID]


Gives:

| RegioId | RegionName | DevicesCount |
|---------|------------|--------------|
|       0 |    Region0 |            5 |
|       1 |    Region1 |            2 |
|       2 |    Region2 |            2 |
|      11 |   Region11 |            1 |
|      12 |   Region12 |            0 |
|      21 |   Region21 |            0 |
|      22 |   Region22 |            1 |