• Questions
• # Running Total

Thanks for the question.

Asked: June 26, 2009 - 2:32 am UTC

Last updated: May 18, 2022 - 2:48 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

Hello Sir,

Could you give a simple example of running total on emp table

FOr example, running total of sum (sal + comm) department wise?

thanks

## and Tom said...

```ops\$tkyte%ORA10GR2> break on deptno skip 1
ops\$tkyte%ORA10GR2> select deptno, empno, sum(sal+nvl(comm,0)) over (partition by deptno order by empno) rt
2  from scott.emp
3  order by deptno, empno;

DEPTNO      EMPNO         RT
---------- ---------- ----------
10       7782       2450
7839       7450
7934       8750

20       7369        800
7566       3775
7788       6775
7876       7875
7902      10875

30       7499       1900
7521       3650
7654       6300
7698       9150
7844      10650
7900      11600

14 rows selected.
```

## Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Rajeshwaran, Jeyabal, June 29, 2009 - 11:51 am UTC

Tom,
```scott@IRADSDB> SELECT EMPNO,
2    ENAME,
3    DEPTNO,
4    SAL,
5    SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO RANGE BETWEEN UNBOUNDED PRECEDING AND CURREN
T ROW) AS RUNNING_TOTAL
6  FROM EMP
7  /

EMPNO ENAME          DEPTNO        SAL RUNNING_TOTAL
---------- ---------- ---------- ---------- -------------
7782 CLARK              10       2450          2450
7839 KING               10       5000          7450
7934 MILLER             10       1300          8750
7369 SMITH              20        800           800
7566 JONES              20       2975          3775
7788 SCOTT              20       3000          6775
7902 FORD               20       3000         10875
7499 ALLEN              30       1600          1600
7521 WARD               30       1250          2850
7654 MARTIN             30       1250          4100
7698 BLAKE              30       2850          6950
7844 TURNER             30       1500          8450
7900 JAMES              30        950          9400

14 rows selected.```

### TO: Rajeshwaran, Jeyabal

Oleksandr Alesinskyy, July 03, 2009 - 5:47 am UTC

It is essentially the same but you have not taken a commission into account (while it was mentioned in the original question) and has expressed the range explicitly (if not specified it is defaulted to the same "RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW").

### Excellent but...

hash, August 01, 2009 - 3:31 pm UTC

Could it be done without using over (partition by deptno order by empno)...?
August 04, 2009 - 12:57 pm UTC

yes it could, but it would be horribly inefficient and incorrect. This is the right way to do it.

You would have to give me a really good reason for wanting another approach. The only ones I can think of - well the ONLY ONE I can think you might be thinking is - "it doesn't work in the database I'm using which isn't Oracle"

### Your question is genuine but...

A reader, August 08, 2009 - 1:49 pm UTC

consider this

create or replace package demo_pkg is
TYPE EMP_rec IS RECORD
(
P_NO VARCHAR2(7),
MNTH DATE,
SUBS_AMT NUMBER,
REFUND_AMT NUMBER,
TR_AMT NUMBER,
AMOUNT NUMBER,
R_TOTAL NUMBER,
PROG_TOTAL NUMBER
);
type refcur is ref cursor return demo_pkg.emp_rec;
procedure get_emp_data(p_p_no varchar2, p_fin_year number, p_cursor in out refcur);
end;
/

create or replace package body demo_pkg is

procedure get_emp_data(p_p_no varchar2, p_fin_year number, p_cursor in out refcur) is
begin
open p_cursor for
SELECT a.*, SUM(R_TOTAL) OVER (PARTITION BY P_NO ORDER BY MNTH) PROG_TOTAL FROM
(
SELECT S.P_NO, S.MNTH, nvl(S.SUBS_AMT, 0)subs_amt,
nvl(S.REFUND_DRAW_AMT, 0)refund_amt,
nvl(T.TR_AMT, 0)tr_amt, nvl(A.AMOUNT, 0)amount,
(case when TO_CHAR(S.MNTH, 'MON') = 'JUN' THEN
(NVL(OPENING_BAL, 0) + nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) + nvl(T.TR_AMT,0)) - nvl(A.AMOUNT, 0)
ELSE
nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) + nvl(T.TR_AMT,0) - nvl(A.AMOUNT, 0)END)r_total
FROM GPF_SUBS S, GPF_TRS T, GPF_ADVANCES A, GPF_STATEMENTS M
WHERE S.P_NO = T.P_NO(+)
AND S.P_NO = A.P_NO(+)
AND S.P_NO = M.P_NO(+)
AND S.MNTH = T.MNTH(+)
AND S.MNTH = A.MNTH(+)
AND FY_GPF_TOTAL.GET_FY(S.MNTH) = FY_GPF_TOTAL.GET_FY(M.YEARS)
)a
where p_no = P_P_NO
and FY_GPF_TOTAL.GET_FY(a.MNTH) = P_FIN_YEAR;

end;

end;
/

The package works great in 9i. But it doesn't get created on 8i (817)(sorry I don't have 8i right now so that I can show you)

Any other alternative approach to the above will be most welcome

thanks
August 11, 2009 - 1:19 pm UTC

use native dynamic sql.

you'll use a weakly typed cursor

type refcur is ref cursor ;

and then you can:

open p_cursor for
'SELECT a.*, SUM(R_TOTAL) OVER (PARTITION BY P_NO ORDER BY MNTH) PROG_TOTAL FROM
(
SELECT S.P_NO, S.MNTH, nvl(S.SUBS_AMT, 0)subs_amt,
nvl(S.REFUND_DRAW_AMT, 0)refund_amt,
nvl(T.TR_AMT, 0)tr_amt, nvl(A.AMOUNT, 0)amount,
(case when TO_CHAR(S.MNTH, ''MON'') = ''JUN'' THEN
(NVL(OPENING_BAL, 0) + nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) +
nvl(T.TR_AMT,0)) - nvl(A.AMOUNT, 0)
ELSE
nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) + nvl(T.TR_AMT,0) -
nvl(A.AMOUNT, 0)END)r_total
FROM GPF_SUBS S, GPF_TRS T, GPF_ADVANCES A, GPF_STATEMENTS M
WHERE S.P_NO = T.P_NO(+)
AND S.P_NO = A.P_NO(+)
AND S.P_NO = M.P_NO(+)
AND S.MNTH = T.MNTH(+)
AND S.MNTH = A.MNTH(+)
AND FY_GPF_TOTAL.GET_FY(S.MNTH) = FY_GPF_TOTAL.GET_FY(M.YEARS)
)a
where p_no = :P_P_NO
and FY_GPF_TOTAL.GET_FY(a.MNTH) = :P_FIN_YEAR' using p_p_no, p_fin_year;

Hash, August 14, 2009 - 10:00 am UTC

Sir, the idea of using a weakly typed cursor doesn't work for me. The problem is that I m going to use this procedure to base my Form on, and Forms don't seem to like weakly type ref cursors.

August 24, 2009 - 7:54 am UTC

```create view XXXX
as
SELECT a.*, SUM(R_TOTAL) OVER (PARTITION BY P_NO ORDER BY MNTH) PROG_TOTAL,
p_no, mnth
FROM
(
SELECT S.P_NO, S.MNTH, nvl(S.SUBS_AMT, 0)subs_amt,
nvl(S.REFUND_DRAW_AMT, 0)refund_amt,
nvl(T.TR_AMT, 0)tr_amt, nvl(A.AMOUNT, 0)amount,
(case when TO_CHAR(S.MNTH, ''MON'') = ''JUN'' THEN
(NVL(OPENING_BAL, 0) + nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) +
nvl(T.TR_AMT,0)) - nvl(A.AMOUNT, 0)
ELSE
nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) + nvl(T.TR_AMT,0) -
nvl(A.AMOUNT, 0)END)r_total
FROM GPF_SUBS S, GPF_TRS T, GPF_ADVANCES A, GPF_STATEMENTS M
WHERE S.P_NO = T.P_NO(+)
AND S.P_NO = A.P_NO(+)
AND S.P_NO = M.P_NO(+)
AND S.MNTH = T.MNTH(+)
AND S.MNTH = A.MNTH(+)
AND FY_GPF_TOTAL.GET_FY(S.MNTH) = FY_GPF_TOTAL.GET_FY(M.YEARS)
)a ```

and query the view

where p_no = :P_P_NO
and FY_GPF_TOTAL.GET_FY(a.MNTH) = :P_FIN_YEAR' using p_p_no, p_fin_year;

you would need to expose those two columns in the view as well.

since you partition by p_no - that part of the predicate can be pushed into the view nicely.

### Doesn't seem to work now

Varun Goel, January 26, 2018 - 2:27 pm UTC

This answer seems to show a running total without using the RANGE clause along with partition by. This doesn't seem to work now. Without the RANGE clause, partition by is summing up all the value in the group. Not sure if I am missing something or has the default setup of RANGE changed since this answer was published.
January 26, 2018 - 3:00 pm UTC

I'm not sure what you're getting at here. Could you clarify? Ideally with an example.

`partition by deptno order by empno`

which comes with an implicit windowing clause of

`range between unbounded preceding and current row`

Which has always been the default afaik.

### Need some help

Vince, May 17, 2022 - 5:45 pm UTC

SUM(EARN_AMT) OVER (ORDER BY EARN_PAYNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Picks up the last row and the running total changes to 118251,.67 when it should be 122476.12. Any thoughts?

AMT Running Total
-112.34 118251.67
122476.12

May 18, 2022 - 2:48 am UTC

We need some data and DDL

# More to Explore

##### Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.