Re: SQL Group By and Question

Table:
cid pid
1 null
2 1
3 1
4 1
5 null

SELECT c_id,

       SUM(1 + (DECODE(LEVEL, 1, -1, 0)))   FROM t_table
 WHERE p_id IS NULL
 CONNECT BY c_id = PRIOR p_id
 GROUP BY c_id

gives results

1       3
5       0

I think this is what you want. This selects looks at c_id's without parents which are only parents themselves or orphans. It then subtracts out the level ones leaving only the c_id and number of others with that as a parent.

Later,
EMaus

In article <8f6qel$g3q$1_at_nnrp1.deja.com>,   Sean <dolans_at_my-deja.com> wrote:
> 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.
>

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