CODEWITHSUNDEEP
sqloracle-databaseoracle11g
gpa
gpa

Reputation: 2451

How to write oracle sql query for remaining totals

I have following data set returned by query is following, i am displaying some rows down below, but actual data returned is over 500k rows.

Date         | amount
01-01-2010   | 200
01-02-2010   | 50
01-03-2010   | 400
01-04-2010   | 50
01-05-2010   | 0
01-06-2010   | 0
01-07-2010   | 100

I would like query to return Remaining Amount column something like this:

Date         | amount   | Remaining
01-01-2010   | 200      | 600
01-02-2010   | 50       | 550
01-03-2010   | 400      | 150
01-04-2010   | 50       | 100
01-05-2010   | 0        | 100
01-06-2010   | 0        | 100
01-07-2010   | 100      | 0

Remaining Amount starts with sum of total amount, which is sum of all records amount column.

Upvotes: 2

Views: 603

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59466

You can also use the Windowing Clause to avoid the SUM(AMOUNT) - (...):

SELECT dt, AMOUNT,
   nvl(sum(amount) over 
      (order by dt desc rows between unbounded preceding and 1 preceding)
   , 0) as REMAINING
FROM yourtable
order by dt

Using the analytic functions should give better performance than correlated subquery, because table is scanned only once.

Upvotes: 3

Neerav Kumar
Neerav Kumar

Reputation: 84

You can use oracle analytic functions

SELECT DATE,
   AMOUNT,
   (SUM (AMOUNT) OVER ()) - (SUM (AMOUNT) OVER (ORDER BY DATE))
      AS REMAINING
FROM TABLE

Upvotes: 2

sgeddes
sgeddes

Reputation: 62841

Here's one approach using a correlated subquery with coalesce:

select y.dt,
  y.amount,
  coalesce((select sum(amount)
   from yourtable y2
   where y2.dt > y.dt),0) as remaining
from yourtable y
order by y.dt

Upvotes: 2

Related Questions