Query to count no of children at every level for a given node

Question

0.00/5 (No votes)

See more:

Hi,
I want to write a query which returns the no of children at every level for a given node in a hierarchy tree.

What I have tried:

I have following Table structure as in sql (don't treat this as a query to create table)

SQL

table Users{
ID int,
UserId int,
ParentID int
}

I have written following query...

SQL

DECLARE @T1 TABLE (
[Level] int,
MemberJoined int
)
declare @level int=1, @UserCount int,  @NodeFor int
while @level <9
begin
select @UserCount = Count(Userid) from Users where ParentID in (select userid from users where ParentID in (@NodeFor ))

insert into @T1 values(@level, @UserCount)

select @NodeFor =  Userid  from Users where ParentID in (select userid from users where ParentID in (@NodeFor ))

set @level=@level+1
End
select * from @T1

It runs correctly for the first level but for the all next level when @NodeFor take only first userid (suppose on first level, user have 3 children then it takes only first record but i want all three)

Posted 4-May-16 2:39am

Er.D.K.Malhotra

Updated 4-May-16 3:54am

Patrice T

v3

Add a Solution

2 solutions

Solution 1

Can't you just use a GROUP BY:

C#

SELECT ParentID, Count(UserID) AS Children FROM Users
GROUP BY ParentID

Posted 4-May-16 2:50am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-04T03:54:00

You don't need to use a loop ...
Have a look at this recursive Common Table Expression

SQL

;WITH usr_CTE AS (
SELECT UserID, ParentID,[Name]
  , 1 as RLevel
  , MtoE = CAST(isnull(ParentID,0) AS VARCHAR(MAX)) + '/' + 
       CAST(UserID AS VARCHAR(MAX))
FROM [Users]
WHERE ParentID IS NULL
UNION ALL
SELECT e.UserID, e.ParentID, e.[Name]
  , RLevel + 1
  , MtoE = MtoE + '/' + CAST(e.UserID AS VARCHAR(MAX))
FROM [Users] e
INNER JOIN usr_CTE ecte ON ecte.UserID = e.ParentID
)
SELECT UserID, EC.ParentID, [Name], RLevel, MtoE
FROM usr_CTE EC

With my dummy data that I set up (a cut down version of Northwind database's Employee table) I get the following results

C#

UserId  Parent  Name             Level  Path to Node
2	NULL	Andrew Fuller	  1	0/2
1	2	Nancy Davolio	  2	0/2/1
3	2	Janet Leverling	  2	0/2/3
4	2	Margaret Peacock  2	0/2/4
5	2	Steven Buchanan	  2	0/2/5
8	2	Laura Callahan	  2	0/2/8
6	5	Michael Suyama	  3	0/2/5/6
7	5	Robert King	  3	0/2/5/7
9	5	Anne Dodsworth	  3	0/2/5/9

(Where I have searched for ParentID IS NULL is where you would use ParentID in (@NodeFor)
So to get the number of children at each level you just change the final select from the CTE:

SQL

;WITH usr_CTE AS (
	SELECT UserID, ParentID,[Name], 1 as RLevel
	FROM [Users]
	WHERE ParentID IS NULL
UNION ALL
	SELECT e.UserID, e.ParentID, e.[Name], RLevel + 1
	FROM [Users] e
	INNER JOIN usr_CTE ecte ON ecte.UserID = e.ParentID
)
SELECT RLevel, COUNT(*)
FROM usr_CTE EC
GROUP BY RLevel

which gave me

Level   Count
1	1
2	5
3	3

Caveat:
Depending on the volume of data in your table you may get an error

Quote:
“Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

To get around this you can use something like

HTML

OPTION (MAXRECURSION 150)

(or however many records you have in your table) That line goes after the GROUP BY clause. Once you are happy that your query is working properly then you can change that to

HTML

OPTION (MAXRECURSION 0)