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. :)
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.