SQL Group By and Question

I don't know if a "smooth" SQL statement will do this or if I have to build the resultset in PL/SQL, but here is the data:

   CAT_ID PARENT_CAT_ID DISPLAY

--------- ------------- --------------------
 49999999               Miscellaneous
        5 49999999      Sub under Misc, #1
        6 49999999      Sub under Misc, #2
        7               Same level as Misc

Question : I would like to know the top level cat_id and a count of how many level 2s there are underneath it: something like:

CAT_ID            COUNT
49999999          2
7                 0

Thanks,
Sean

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 08 2000 - 00:00:00 CDT