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

Sending one file to two inputs

javascript php forms

I have a little problem and i cant solve it. Below is my form. The question is: how can i put image which was put in input where is ajaximage.php to another form to a hidden input. I want the image...

How can I determine the mime-type of a document using javascript only?

javascript internet-explorer mime-types

I need to determine the mime-type of certain files that get returned from the service, I do not know their file extensions or what their type is before hand, basically we retrieve documents from th...