How to find the count of child nodes per parent node using same t-sql?

I have following resultset:

Parent  Child   Childcount
1          1         2
1          2    
2          1         3
2          2    
2          3    
.       
.
.
.
.

By using groupby clause I am able to fetch Parent Child but not the child count in the same query? I can get child count for a parent using separate query, but I want to get the entire set in single query How can I achieve the same? So far, I have tried following query:

SELECT parent.NAME AS [Parent]
    ,child.NAME AS [Child]
    ,count(row_number() OVER (
            ORDER BY parent.NAME
            )) AS childcount
FROM table1 parent
INNER JOIN child
    ON parent.id = child.id
GROUP BY parent.NAME
    ,child.NAME
ORDER BY parent.NAME

This query throws me the error.Can anyone please provide me with the help? Thanks. :)

Answers


You're asking for two things, one is "what is every parent/child combination", and the other is "for every parent, how many children does it have". It is basically two queries.

You could join to the child table a second time, and add COUNT(child2.id) to your select.


Need Your Help