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


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.