Solved

Running Total Query in Oracle

Posted on 2003-12-02
3
852 Views
Last Modified: 2008-03-17
8i, Standard, no Analytic functions available....

I need to calculate a running total column which would be the sum of a column for all preceding rows including the current row. for example, if my data was:

ID VAL
1   100
2   100
3   50
4  25

and I was doing a running total on VAL, Ordered By ID, then my result set would be
ID VAL RUNNING_TOTAL
1   100   100
2   100   200
3   50    250
4   25    275

etc, etc.

Any ideas?
0
Comment
Question by:markag
3 Comments
 
LVL 2

Accepted Solution

by:
IanBlundell earned 500 total points
ID: 9860177
Given a table called exp_test, try something like this:

select      it1.id
,            it1.val
,            (select      sum(it2.val)
             from      exp_test it2
             where      it2.id <= it1.id)            
from exp_test it1
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9860191
How about:

select id, val, (select sum(val) from mytable t2 where t2.id <= t1.id) running_total
from mytable t1
order by id;
0
 
LVL 13

Expert Comment

by:riazpk
ID: 9866362
Try this:


select id,val, sum(val) over (order by id) Running_Total
from mytable
order by id
/



Love Analytics...


Riaz
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question