Solved

Cumulative totals in orale sql

Posted on 2011-02-23
2
556 Views
Last Modified: 2012-06-21
Is it possible to calculate cumulatively in oracle, i.e. using the results of the last row for calculating the values in the current row?
For example I want my query to return the following:
Month      col1      _      col2      _      col3
jan      1      _      100      _      100 * 1
feb      2      _      200      _      200 * 2 + 100
mar      3      _      300      _      300 * 3 + 500
apr      4      _      400      _      400 * 4 + 1400
may      5      _      500      _      500 * 5 + 5500

In col3 above, for feb I want to use the result returned for jan (100 * 1=100), for mar I am using the result returned for feb(200 * 2 + 100=500) and so on.

Thanks in advance.
0
Comment
Question by:subratoc
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 34959902
select col1, col2, sum(col2*col1) over (order by col1)  
  from yourtable
order by col1
0
 

Author Comment

by:subratoc
ID: 34965876
Sorry, I should have given a slightly different example. I meant to say that I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself. The revised example would be:
Month      col1      _      col2      _      col3
jan      1      _      100      _      100 * 1
feb      2      _      200      _      100 * 2 + 200
mar      3      _      300      _      400 * 3 + 300
apr      4      _      400      _      1500 * 4 + 400
may      5      _      500      _      6400 * 5 + 500
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finance  implemmentation   On-Premises vs Manage service vs Cloude 3 77
Oracle SQL 6 48
Queries 15 34
Oracle Listener Not Starting 11 25
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now