Re: Summing hierarchical data SQL

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 05 Oct 2006 10:35:07 +0200
Message-ID: <4524c3f1$0$13086$9b4e6d93@newsspool2.arcor-online.net>

Michel Cadot schrieb:
> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159993002.453179.263340_at_e3g2000cwe.googlegroups.com...
>
> Mike C wrote:

>> Michel Cadot wrote:
>>> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159989707.458848.89750_at_c28g2000cwb.googlegroups.com...
>>> I've already used the connect by to get the data in this format....
>>>
>>> -----------------------
>>>
>>> - Don't top post
>>> - So go on, you can easely get the sum over the hierarchy with a connect by.
>>>
>>> Regards
>>> Michel Cadot
>> This doesn't really need a CONNECT BY as it only needs to look at 1
>> level below.  To get the sum for level 1, it only needs to look at
>> level 2, etc.  If I could "easily get the sum" I would have done it and
>> wouldn't have posted to the group!

>
> I know I can write it something like this, but I was hoping to not have
> to repeat code and do it using one of the analytic fucntions if
> possible.
>
> SELECT NAME, level, decode(level,4,t1.frequency,t2.frequency) from
> table1 t1,
> (select parent_id, sum(frequency) FROM table1 group by parent_id) t2
> WHERE t1.id=t2.parent_id(+)
>
> --------------------------
>
> Analytic functions are useless here.
> From your exemple, you have to walk through the whole hierarchy
> and not just one level (else how do you 30 for id 1?).
> And connect by is the only way to go through a hierarchy.
> I don't think you can get something with your query.
>
> Regards
> Michel Cadot
>
>

The straightforward approach would be probably like

SELECT Empno,

        Ename,
        Sal + Nvl((SELECT SUM(Sal)
                  FROM   Scott.Emp i
                  CONNECT BY PRIOR Empno = Mgr
                  START  WITH o.Empno = i.Mgr),
                  0) Total_SubTree_Sal

FROM Scott.Emp o

Not sure, about the performance with correlated hierarchical subquery, whereas with not too big volumes of data ( and not too deep hierarchy) should be ok.

It might be interesting, if this result can be achieved without correlated subquery however...

Best regards

Maxim Received on Thu Oct 05 2006 - 03:35:07 CDT