sum on tree structure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sum on tree structure

  1. #1
    Join Date
    Jan 2002
    Posts
    5
    i want to get sum in tree for each node with childs
    tree :
    A(1)_B(4)
    |
    |_C(1)_E(3)
    |
    |_D(2)

    sum num for each node and childs
    ---------------------
    A 11(4+4+2+1)
    B 4
    C 4(1+3)
    D 2
    E 3

    ------------------------

    structure is:
    srl
    ogcost_srl --relation to parent
    title
    ------------------------
    sql:
    select title,f_sum(srl)
    from ogcostt a
    -------------------------
    f_sum:

    f_sum(cur_srl numer) return number is
    sum_num number(6);
    begin
    select sum(num)
    into sum_num
    from admin.ogcostt
    start with srl=cur_srl
    connect with prior srl=ogcost_srl);
    return sum_num;
    end;

    ------------------------
    this method calculate repeated sum and not effichient
    and time consuming
    can i use from analytic functin for efficient sql?

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    subquery?

    If you have Oracle 8.1.6 or above then you can use the following form of subquery:

    select title,
    (select sum(num) from ogcostt
    connect by prior srl=ogcost_srl
    start with srl=a.srl)
    from ogcostt a;

    I don't think, though, that it is much more efficient. Try.
    Hope it helps.

    Tomaž

  3. #3
    Join Date
    Jan 2002
    Posts
    5
    hi tomazZ
    thank you for your answer.
    but this approach also repeating sum for high level in tree;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width