Re: Summing hierarchical data SQL

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 5 Oct 2006 17:36:10 +0200
Message-ID: <4525266a$0$11324$426a74cc@news.free.fr>

"Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 4524c3f1$0$13086$9b4e6d93_at_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

I don't think so.
A comment on your query: why starting with o.empno=i.mgr and not o.empno=i.empno? This prevent from adding current sal and testing if sum(sal) of children is null or not:

SQL> select * from t order by id;

        ID NAME PARENT FREQUENCY ---------- ---------- ---------- ----------

         1 USA
         2 CA                  1
         3 LA                  2         10
         4 SF                  2         15
         5 IL                  1
         6 Chicago             5          5

6 rows selected.

SQL> select id, name, parent,

  2         ( select sum(frequency)
  3           from t t2
  4           connect by prior id = parent
  5           start with t2.id = t1.id ) frequency

  6 from t t1
  7 /

        ID NAME PARENT FREQUENCY ---------- ---------- ---------- ----------

         1 USA                           30
         2 CA                  1         25
         3 LA                  2         10
         4 SF                  2         15
         5 IL                  1          5
         6 Chicago             5          5

6 rows selected.

Regards
Michel Cadot Received on Thu Oct 05 2006 - 10:36:10 CDT