Re: SQLPlus: How to do Running Totals?

From: Oracle <oracle_at_AFTAC.GOV>
Date: Fri, 9 Jul 1993 14:34:40 GMT
Message-ID: <C9wJ5t.2HL_at_rayleigh.aftac.gov>

In article <C9urDx.127_at_mach1.wlu.ca>, derwin_at_mach2.wlu.ca (Daryl Erwin) writes:
|> This seems like a trivial request, but I cant figure it out...
|> I want....
|>
|> FieldA RunTot <--- Computed Field
|> ------ ------
|> 1 1
|> 1 2
|> 2 4
|> 2 6
|>
|>
|> Is there a simple answer here?
|> What compbination of 'BREAK ON' & 'COMPUTE' do I need?
|> --
|> Daryl Erwin Net Address: derwin_at_mach2.WLU.CA
|> Information Systems Surface Mail: P4-1, Main Campus
|> Wilfrid Laurier University, Waterloo, Ontario
|> Bus:(519) 884-1970 x2910 !edisni deppart m'I pleH

I really don't have a simple answer to this request but I have made a PL/SQL script that seems to fit the bill. I created a table test with only a single column of numbers (VAL) and ran this and it seems to be what you wanted. Script is as follows:

create table ADD_TEMP
  (VAL_1 NUMBER, VAL_TOT NUMBER)
/

declare
  cursor query is select * from test;

  this_row        query%rowtype;
  total           number;

begin
  open query;
  fetch query into this_row;
  total := this_row.val;
    insert into ADD_TEMP (VAL_1, VAL_TOT)     values (this_row.val, total);
  loop

     fetch query into this_row;
     exit when query%notfound;
       total := total + this_row.val;
       insert into ADD_TEMP (VAL_1, VAL_TOT)
       values (this_row.val, total);

  end loop;
end;
.
/

select * from ADD_TEMP
/

drop table ADD_TEMP
/

When ran against table test it gave the following results:

SQL> _at_test

Table created.

PL/SQL procedure successfully completed.

     VAL_1 VAL_TOT
---------- ----------

         1          1
         3          4
         6         10
         2         12
        10         22
         5         27

6 rows selected.

Table dropped.

SQL> Like I say it's not really a simple solution but it is a workable one...

hope it helps a little .......

---

Harry Adams                                     Net Address:  harry_at_AFTAC.GOV

Air Force Technical Applications Center
Patrick AFB, FL

(407) 494-5481                  ....no real expert on this end.......

Received on Fri Jul 09 1993 - 16:34:40 CEST