How to calculate the number of immediate children for each node in an adjacency list

Question

I have hierarchical data that I represent using the adjacency list model.

TABLE
    ID
    parentID
    title

I am wondering, what is the simplest way to SELECT the number of immediate children for each node? If possible, I'd like to do this in a single select, yielding a resultset like so...

RESULTS...
ID     title     childCount
1      test1     10
2      test2     2
3      test3     0
etc...

Thanks for your advice!

Answer 1

Without the title,

SELECT parentID as ID, COUNT(ID) AS childCount
  FROM Table
  GROUP BY parentID

If you do want the title I think you need a self-join (probably way slower):

SELECT t1.ID as ID, t1.Title as Title, COUNT(t2.ID) as childCount
  FROM Table t1
  LEFT OUTER JOIN Table t2
  ON t1.ID = t2.parentID

Answer 2

I think Alex forgot 'Group By t2.parentID'

also you can try:

SELECT t1.ID as ID, t1.Title as Title, COUNT(t2.ID) as childCount 

FROM Table t1 INNER JOIN Table t2 ON t1.ID = t2.parenID GROUP BY t2.parentID