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

[Mauro Pagano]

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

[NetComrade]

explore connect by clause.
.......
We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc)
remove NSPAM to email

[Mauro Pagano]

On Mar 18, 5:50 pm, NetComrade <netcomradeNS...@bookexchange.net>
wrote:

NetComrade,
are you referring to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#i1007332
?
Regards
Mauro

[NetComrade]

This is probably a better place to start:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

I never actually wrote one, but that's the way to go :)
Also search asktom.oracle.com for "connect by" for examples

[Mauro Pagano]

On Mar 18, 6:29 pm, NetComrade <netcomradeNS...@bookexchange.net>
wrote:

> >are you referring tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudo...
>
> This is probably a better place to start:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/querie...

>
> I never actually wrote one, but that's the way to go :)
> Also search asktom.oracle.com for "connect by" for examples
> .......
> We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc)
> remove NSPAM to email

Netcomrade,
my post is just a "joke" to find different solution to achieve it.
The straight way to do what I asked is to use connect_by_isleaf (it's
why I've pointed out that doc page).
My question is "in your opinion, which could be another way to achieve
it?"
Regards
Mauro

[Maxim Demenko]

Mauro Pagano schrieb:

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

[Maxim Demenko]

NetComrade schrieb:

> I didn't catch he only wanted direct children

He didn't state it, however it is the only conclusion i can make,
comparing his input data and expected output.

Best regards

Maxim