Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sum of values (for each row, generate sum based on a set of rows)

elmasduroNov 9 2018 — edited Nov 16 2018

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

This post has been answered by Frank Kulash on Nov 9 2018
Jump to Answer
Comments
Post Details
Added on Nov 9 2018
6 comments
2,239 views