CODEWITHSUNDEEP
sqloracle-database
mayus
mayus

Reputation: 53

oracle query sequential summation per rows

I would like to ask to make a query like the following table?

-------------------------------------
ID   Date        Input  Output  Total
-------------------------------------
1   2016-05-01  10       0      10
1   2016-05-02  2       -1      11
1   2016-05-03  0       -5      6
1   2016-05-04  0       -1      5

assume I have a table "table_a" that has a column (ID, name, date, input, output). how to create query with results as above.

Upvotes: 1

Views: 127

Answers (2)

sagi
sagi

Reputation: 40481

Use SUM() OVER(..) window function which produce cumulative sum :

SELECT t.id,t.date,t.input,t.output,
       SUM(t.output+t.input) OVER(PARTITION BY t.ID ORDER BY t.date) as Total
FROM YourTable t

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use SUM OVER function:

SELECT *,
    SUM(Input + Output) OVER(PARTITION BY ID ORDER BY Date) AS Total
FROM table_a

Upvotes: 1

Related Questions