Re: Count child of every node in hier table reading source just once

Mauro Pagano schrieb:
> Hi guys,
> I'm trying to write a SQL statement to count how many child every node
> in a hierarchical table has.
> I want to perform this operation reading source table only one time.
>
> Following an example of source table:
>
> CREATE TABLE TEST_HIER
> (
> ID NUMBER,
> PID NUMBER
> );
>
> Insert into TEST_HIER
> (ID, PID)
> Values
> (1, 0);
> Insert into TEST_HIER
> (ID, PID)
> Values
> (2, 1);
> Insert into TEST_HIER
> (ID, PID)
> Values
> (3, 1);
> Insert into TEST_HIER
> (ID, PID)
> Values
> (4, 2);
> Insert into TEST_HIER
> (ID, PID)
> Values
> (5, 3);
> COMMIT;
>
> And expected result is:
>
> PID CHILD
> ---------- ----------
> 0 1
> 1 2
> 2 1
> 3 1
>
> Any idea?
> Thanks a lot
> Mauro

As you are counting only direct children, the number of children is exactly the same, as every parent is referenced. So, you don't need connect by at all, simple

select pid,count(*)
from test_hier t
group by pid

gives you expected output.

Best regards

Maxim Received on Tue Mar 18 2008 - 15:20:26 CDT