subratoc
asked on
cumulative figures in oracle sql
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 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200),
for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. 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.
Thanks in advance.
For example I want my query to return the following:
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
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200),
for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. 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.
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think in this case function is better and you can easily modify it in future for any changes/modifications though it may be possible to directly do it with a SQL query but the complexity of the SQL query will end in difficult situation to modify for future needs.
LAG (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)
HTH
Ivo Stoykov
PS: here there is some help about
Open in new window