Sql queries to manage hierarchical or parent-child relational counts in SQL server

For a Parent-Child self-referencing table, there are going to be different routines to be done:

Build the hierarchical structure
You already have the database table, you are going to need a recursive function to generate a dataset that reflects how that structure looks as a tree. There are many examples of sample code for this available, including this one with some management functions located here
SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server[^]

Count the children for any particular parent
This is going to be a simple one; basically you need to do a count of children for each parent

Tips & Hints
When my web applications using parent-child structures are edited; I fire off a stored procedure that rebuilds the hierarchy and saves it into a new table so that the recursion does not need to be done at every call.
On the parent-cild table itself, there are fields that contain the counts for children as well as how many items are in that category.

---

Update
Sample Code for Counting the Children
Please note the square brackets around [Name]; this is because it is a reserved word. It is generally recommended not to use these when you are designing tables etc as it can cause problems if the square brackets are omitted

SELECT    p.[Name], Children = Count(c.ParentID)
FROM      DocuTree  p
LEFT JOIN DocuTree  c ON p.ChildID = c.PhildID
GROUP BY  p.[Name]