hi everybody,
i need help on adding values based on number of rows. consider my dataset
with dataset as
(
select 0 row_num, 201 total1, 1077 total2 from dual union all
select 1 row_num, 419 total1, 3890 total2 from dual union all
select 2 row_num, 468 total1, 920 total2 from dual union all
select 3 row_num, 521 total1, 338 total2 from dual union all
select 4 row_num, 482 total1, 144 total2 from dual union all
select 5 row_num, 509 total1, 65 total2 from dual union all
select 6 row_num, 533 total1, 32 total2 from dual union all
select 7 row_num, 543 total1, 27 total2 from dual union all
select 8 row_num, 554 total1, 38 total2 from dual union all
select 9 row_num, 474 total1, 28 total2 from dual union all
select 10 row_num, 507 total1, 23 total2 from dual union all
select 11 row_num, 535 total1, 29 total2 from dual union all
select 12 row_num, 482 total1, 31 total2 from dual union all
select 13 row_num, 320 total1, 30 total2 from dual union all
select 14 row_num, 109 total1, 26 total2 from dual union all
select 15 row_num, 53 total1, 14 total2 from dual union all
select 16 row_num, 26 total1, 24 total2 from dual
)
i want to output the following
row_num total1 total2 total3
0 201 1077 6736
1 419 3890 6710
2 468 920 6657
3 521 338 6548
4 482 144 6228
5 509 65 5746
6 533 32 5211
7 543 27 4704
8 554 38 4230
9 474 28 3676
10 507 23 3133
11 535 29 2600
12 482 31 2091
13 320 30 1609
14 109 26 1088
15 53 14 620
16 26 24 201
column total3 has some logic. the way it works is that i will look at the first row total2=1077 and then sum all the values from total1 from 201 all the way down to 26.
this will give total3=6736
then oracle will move to row with total2=3890. in this case i want to sum up total1 from the first row 201 all the way down to row with total1=53, filtering out value 26.
that will give total3=6710
so in general, as oracle move down the rows(total2) from top to bottom, the summation of total1 will be from top to bottom minus some rows.
i was thinking lead function can be helpful but then how do i traverse through total1.
also i was experimenting with unbound and following but couldnt figure out.
can someone help me write a query that produce output above? i am using oracle 11g