connect by and aggregate functions

Say I have some hierarchical data like

SQL> select * from hdata;

      ITEM TARGET VALUE
---------- ---------- ----------

         0                     0
         1          0          5
         2          1          7
         3          0         12
         4          1          9
         5          3          2

The first is a dummy row to allow getting all roots with 'start with item = 0' as 'start with item = null' doesn't work.

I can reproduce a tree with a query like

column item format a10;
select lpad(' ',level)||item item, target, value from hdata connect by prior item = target start with target = 0;

ITEM           TARGET      VALUE        sum       avg
---------- ---------- ----------        imaginary imaginary
 1                  0          5        21         7
  2                 1          7         7         7
  4                 1          9         9         9
 3                  0         12        16         8
  5                 3          4         4         4
 

I'd like to be able to to obtain sums, averages and the like for all nodes in a subtree, as in the imaginary results above. I'd also like to be able to sort nodes within a subtree by some value calculated from a node and its children.

I could do this by executing the above query, reading it into my own data structures and doing the tree aggregate calculations and sorting there. Doesn't seem very efficient.

Any hints or recommendations on how to proceed?

The 'value' above is actually a score from an intermedia text search, so it won't help to precalculate anything, as all values will change with the text query.

Thanks...
Mike

--
                                       See From: and Organization: above.