Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

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

Last updated: January 26, 2018 - 3:00 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

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 Chris 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

  (6 ratings)

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

Comments

What about this?

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

Tom,
What about this one with our old Analytics.
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
      7876 ADAMS              20       1100          7875
      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)...?
Tom Kyte
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
Tom Kyte
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.

Tom Kyte
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

since your predicate is:

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.
Chris Saxon
January 26, 2018 - 3:00 pm UTC

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

Anyway, the original answer has:

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.

More to Explore

Analytics

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