DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Add each row?

  1. #1
    Join Date
    Feb 2003
    Location
    Pak
    Posts
    64

    Add each row?

    I have a table A which has the following data:

    A
    ----
    1
    2
    3
    4
    9
    2

    I need the following output thruogh SQL.

    ACCUMULATIVE
    -------------
    1
    3
    6
    10
    19
    21

    It will hold the sum of the last row and add in it the next row.
    I hope, i have convayed the question clearly.

    Regards,

    Nametullah kalair
    Always Remember Power is Your.

  2. #2
    Join Date
    Feb 2005
    Posts
    49
    is it possible to use a procedure for handling it ?

    Thanks
    Aiman Al-Jumoay

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Analytic functions are the key to this ...
    Code:
    SQL> set feedback off
    SQL> create table a (pos number, a number);
    SQL> insert into a values (1,1);
    SQL> insert into a values (2,2);
    SQL> insert into a values (3,3);
    SQL> insert into a values (4,4);
    SQL> insert into a values (5,9);
    SQL> insert into a values (6,2);
    SQL>
    SQL> select sum(a) Over (Order By pos range unbounded preceding)
      2  from a
      3  order by pos;
    
    SUM(A)OVER(ORDERBYPOSRANGEUNBOUNDEDPRECEDING)
    ---------------------------------------------
                                                1
                                                3
                                                6
                                               10
                                               19
                                               21
    I added a pos column to indicate the order in which you wanted the result set returned and the cumulative sum calculated.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Feb 2005
    Posts
    49
    Dear Developer,
    the solution is a bit too long and stupid I would rather if your stick with the Position solution posted already , but anyway it was a try

    --------------------------------------------------------------
    CREATE OR REPLACE procedure calc_a
    is
    x number ;
    out number ;
    begin
    select count( id ) into x from a ;
    out := 0 ;
    for i in 1..x loop
    select sum( id ) into out from a
    where dbms_rowid.rowid_row_number(rowid) + 1 between 1 and i;
    dbms_output.PUT_LINE( out ) ;
    end loop ;
    end ;

    Thanks
    Aiman Al-Jumoay

  5. #5
    Join Date
    May 2005
    Posts
    2
    Hi,
    I think best way is :
    select sum(id) over(order by rowid) ACCUMULATIVE
    from a

    ACCUMULATIVE
    ===========
    1
    4
    6
    10
    18
    20
    29

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "order by rowid"?

    Yuk.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2003
    Location
    Pak
    Posts
    64
    bhavesh_09, Thanks my problem has been resolved. It was a smartest way.

    Regards,

    Nametullah kalair
    Always Remember Power is Your.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by nametullah
    bhavesh_09, Thanks my problem has been resolved. It was a smartest way.

    Regards,

    Nametullah kalair
    Relying on rowid for ordering is smart, eh? Interesting idea.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Feb 2003
    Location
    Pak
    Posts
    64
    Relying on rowid for ordering is smart, eh? Interesting idea.

    Yeh, i know what r u thinking slimdave, but problem is resolved. But i m also agree with u and ur example. Thanks for helping me.

    Regards,

    Nametullah kalair
    Always Remember Power is Your.

  10. #10
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by nametullah
    Yeh, i know what r u thinking slimdave, but problem is resolved. But i m also agree with u and ur example. Thanks for helping me.

    Regards,

    Nametullah kalair
    Not a general solution though.
    Slimdave's solution is the most general one.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

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