1 SELECT Div_ID,
2 Div_Name,
3 (SELECT Count(Parent_ID)
4 FROM Division d2
5 WHERE Parent_ID=d1.div_id) as ChildCount
6 FROM Division d1
7* WHERE Parent_ID=0
scott@AMSAA>
scott@AMSAA> /

DIV_ID DIV_NAME CHILDCOUNT
---------- ---------- ----------
1 div a 3
5 Div B 1
7 Div C 0