How to find count of Child using parentId

Question

How to find the count of children in each row?

For example:

   1 ROW COUNT=1
   2 ROW COUNT=0 
   ...

and so on. In the next column

Answer 1

You can do this using recursive CTE, but it should be done with simple join. First, find the count of each node, excluding node without parents:

SELECT [ParentID] 
      ,COUNT(*)
FROM MyTable 
WHERE [ParentID] <> 0
GROUP BY [ParentID];

If this is OK, just join to the initial table:

SELECT *
FROM MyTable T1
LEFT JOIN
(
    SELECT [ParentID] 
          ,COUNT(*) AS [all_childs]
    FROM MyTable 
    WHERE [ParentID] <> 0
    GROUP BY [ParentID]
) T2
    oN T1.[parentID] = T2.[ParentID];