How to count group by a tree use ORACLE?

Question

Right now I have a dataset like this; (8 records)

the tree like as follows:

root -- 0
--child 100
  --sub child 108
     ---sub ...(maybe has more level)
--child 200
  --sub child 205
--child 400
  --sub child 423

And I want to count all sum records each by ever child (not sub child, sub child's record should calculate into it's father or grandfather until the first level child node).

so the result should like:

node    counts
100       5
200       1
400       2

But when I use start with connect by and with group by keywords I cannot get the expected result.

my sql is as follows:

select cid as node,count(1) as counts 
from (one subselect for get the 8 records) a 
start with a.pid = '0' 
connect by prior a.cid = a.pid) t group by cid;

result is empty.. who can help me? or who knows the detail for the oracle group by keywords works when used together with a tree structure?

What result do you get with your present query? In the group by clause: "group by aid", what is aid? — ramana_k, Sep 29 '15 at 06:51
how can `--child 400` is child of root `0` when there is no entry of `400 0` in your dataset. — Utsav, Sep 29 '15 at 07:36
the dataset is not the tree table. it was only a normal data table for record s. — alardear, Sep 29 '15 at 07:47
every node will generate some records..and I want to count how much for each first node has generated. — alardear, Sep 29 '15 at 07:51
but if the records was generated by its children or subchildren .it should also counts for the first node. — alardear, Sep 29 '15 at 07:52

score 0 · Answer 1 · answered Sep 29 '15 at 08:47

Try this:

SELECT top_cid, Count(1) FROM (
  WITH
    parent_child as (
      select distinct a.cid, a.pid from a
      union  
      select a.pid, 0 from a
      where a.pid<>0 and not exists (
        select 1 from a a1 where a1.cid=a.pid
      )
    ),
    tree as (
      select level as lvl,
        --connect_by_root in 10g and above
        replace(sys_connect_by_path(decode(level, 1, cid), '~'), '~') AS top_cid, 
        pid, cid 
      from parent_child
      start with pid = 0
      connect by prior cid = pid(+)
    )
  select tree.top_cid, a.pid, a.cid 
  from a, tree
  WHERE a.cid=tree.cid
) GROUP BY top_cid

I get the result is that: 400-2 , 0-6 not the except result – alardear Sep 29 '15 at 09:30 — alardear, Sep 29 '15 at 09:30

How to count group by a tree use ORACLE?

1 Answers1