CODEWITHSUNDEEP
sql-servert-sqlhierarchy
Basil Jacob
Basil Jacob

Reputation: 117

How to find count of Child using parentId

enter image description here

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

Upvotes: 2

Views: 1974

Answers (2)

Vaso Miruashvili
Vaso Miruashvili

Reputation: 109

This should be okay, you need isnull() function when ROW COUNT=0 

 SELECT 
        PA.ID, 
        PA.Title_Name, 
        ISNULL(P.COUNTT,0) CountOfID 
 FROM #My_Table PA
 LEFT JOIN (
             SELECT COUNT(*) COUNTT, Parent_ID from #My_Table GROUP BY Parent_ID
             ) as P on P.Parent_ID = PA.ID

Upvotes: 1

gotqn
gotqn

Reputation: 43636

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];

Upvotes: 3

Related Questions