CODEWITHSUNDEEP
sqloracle-database
enginner ava
enginner ava

Reputation: 19

SQL query to calculate sum of row with previous row

This is my table 

date     count of subscription per date
----      ----------------------------
21-03-2016      10
22-03-2016      30
23-03-2016      40

Please need your help, I need to get the result like below table, summation second row with first row, same thing for another rows:

date     count of subscription per date
----      ----------------------------
21-03-2016      10
22-03-2016      40
23-03-2016      80

Upvotes: 2

Views: 8657

Answers (3)

Dart XKey
Dart XKey

Reputation: 54

Select sum(col1) over(order by date rows between unbounded preceding and current row) cnt from mytable;

Upvotes: 2

Nick
Nick

Reputation: 10143

SELECT t.date, (
    SELECT SUM(numsubs) 
    FROM mytable t2 
    WHERE t2.date <= t.date
) AS cnt
FROM mytable t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can do a cumulative sum using the ANSI standard analytic SUM() function:

select date, sum(numsubs) over (order by date) as cume_numsubs
from t;

Upvotes: 3

Related Questions