Running totals in SQL*Plus: a 7.1 solution

To all those who will have seen my previous post on this topic and pointed out the error, but whose corrections have not yet got to me (who needs time-travel to get confused with grammar) :

You are quite correct: the supplied function works in much the same was as the pseudo-column ROWNUM: i.e. things go wrong if you have an 'order by' clause in the SQl statement.

Since this is the case, you are advised that you should not use this function casually. However, if you do want to use it, I have written a modified version which recognises and takes advantage of its similarity to ROWNUM: basically, for non-'group by' statements, you do not need to call the 'reset total', the function resets its own running total if the row number is 1.

The following package overloads two variants of the function, and the text below shows a sample use.

create or replace package accumulate as

    function get_running(new_value in number) return number;     pragma restrict_references(get_running, WNDS, RNDS);

    function get_running(new_value in number, row in number) return number;     pragma restrict_references(get_running, WNDS, RNDS);

    procedure reset_total;
end accumulate;
/

create or replace package body accumulate as running_total number := 0;

function get_running(new_value in number,row number) return number is begin

    if (row = 1) then

        running_total := new_value;
    else

        running_total := running_total + new_value;     end if;
    return running_total;
end get_running;

function get_running(new_value in number) return number is begin

    running_total := running_total + new_value;     return running_total;
end get_running;

procedure reset_total is
begin

    running_total := 0;
end reset_total;

end accumulate;
/

drop table t1;
create table t1 (c1 varchar2(2),n1 number);

insert into t1 values ('x',4);
insert into t1 values ('w',3);
insert into t1 values ('y',2);
insert into t1 values ('x',5);
insert into t1 values ('w',2);
insert into t1 values ('y',1);
insert into t1 values ('x',4);

==========================

select n1, accumulate.get_running(n1,rownum) from t1;

        N1 ACCUMULATE.GET_RUNNING(N1,ROWNUM)                                    
---------- ---------------------------------                                    
         4                                 4                                    
         3                                 7                                    
         2                                 9                                    
         5                                14                                    
         2                                16                                    
         1                                17                                    
         4                                21                                    

7 rows selected.

execute accumulate.reset_total;

PL/SQL procedure successfully completed.

select c1,sum(n1),accumulate.get_running(sum(n1)) from t1 group by c1;

C1    SUM(N1) ACCUMULATE.GET_RUNNING(SUM(N1))                                   
-- ---------- -------------------------------                                   
w           5                               5                                   
x          13                              18                                   
y           3                              21