0

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

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 |
  • Consider that i have another table named Users which is similar to Devices table. please update your answer to support counting of Users also. – r.zarei Dec 20 '17 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.