How to count hierarchical rows count with a CTE

Question

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 |
+---------+------------+--------------+

Answer 1

To answer your supplementary question:

"Consider that i have another table named Users which is similar to Devices table. please update your answer to support counting of Users also."

(Note: I'm not sure that adding supplementary increments to questions is quite how stackexchange is supposed to work, and you haven't supplied an updated fiddle for this - so this is untested. But here goes anyway...)

You just need to change a couple of lines in the body of the select, thus:

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

Answer 2

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 |