title: Hierarchy Query - Aggregate the nodes
body: <p>Need an assistance in hierarchy query , I have 2 tables , one holds hierarchy  structure between the elements and second one holds active alarm per element .    </p>
  
  <pre><code>create table hrc (parent_id number, child_id number);
  insert into hrc (parent_id,child_id) values (null,1);
  insert into hrc (parent_id,child_id) values (1,2);
  insert into hrc (parent_id,child_id) values (2,0);
  insert into hrc (parent_id,child_id) values (0,76292);
  insert into hrc (parent_id,child_id) values (0,96689);
  insert into hrc (parent_id,child_id) values (0,12429);
  insert into hrc (parent_id,child_id) values (0,10697);
  insert into hrc (parent_id,child_id) values (0,60856);
  insert into hrc (parent_id,child_id) values (0,99460);
  insert into hrc (parent_id,child_id) values (76292,57120);
  insert into hrc (parent_id,child_id) values (12429,25152);
  
  
  commit
  /
  create table alarms (id number,element_id number) ;
  
  insert into alarms (id,element_id) values (1,0);
  insert into alarms (id,element_id) values (2,0);
  insert into alarms (id,element_id) values (3,0);
  insert into alarms (id,element_id) values (4,76292);
  insert into alarms (id,element_id) values (5,76292);
  insert into alarms (id,element_id) values (6,57120);
  insert into alarms (id,element_id) values (7,57120);
  insert into alarms (id,element_id) values (8,57120);
  insert into alarms (id,element_id) values (9,25152);
  insert into alarms (id,element_id) values (10,25152);
  insert into alarms (id,element_id) values (11,12429);
  commit;
  </code></pre>
  
  <p>The next query represent the hierarchy</p>
  
  <pre><code>with q (parent_id,child_id, hierlevel,root) as
  ( 
  select parent_id,child_id, 0,child_id root 
  from   hrc 
  where  parent_id  = 0
  UNION ALL  
  select  hrc.parent_id,hrc.child_id ,q.hierlevel + 1 , q.root 
         from   hrc
           join
           q
           on (q.child_id=hrc.parent_id)
  
  )select * from q  
  </code></pre>
  
  <p>I want to join ( left outer ) to alarms tables and get the count of the alrams per element but just for the first level for example , for choosing parent_id=0 
  The required output is : </p>
  
  <p>COUNT                    ROOT</p>
  
  <hr>
  
  <pre><code>                   5      76292
                     3      12429
                     0      60856
                     0      10697
                     0      99460
                     0      96689
  </code></pre>
  
  <p>Element 76292(5)  - 3 of his alarms of his own  and his child (57120) has 2 .
    for  12429 (3) -  1 of his own and 2 of his child . 
  The rest has 0 alarms . </p>
  
  <p>The preferred way is using CTE and not connect by . </p>
  
  <p>Thanks for the help .  </p>