The Oracle Sum fuction is very useful. Also analytical Oracle Sum functions are available. Using sum(<column>) over (partition by <column>): with this oracle sum function, you can sum over a range within the result set.
Sample: I have 526 Rows of payed bills, unfortunatelly within this view I get bills with their position in the same view (not normalized). With this oracle sum function, I calculate a sum of all positions within its bill.
select
persnr
,billnr
,positionnr
,betrag
,currency
,sum(betrag)
over (partition by persnr, rechnungnr
order by persnr, to_number(billnr),to_number(positionnr)) as oracle_sum -- oracle sum function from billing_view
where 1=1
and persnr = 37797
Oracle Sum Result Set
PERSNR BILLNR POSITIONNR BETRAG CURRENCY ORACLE_SUM 37797 900004191 1 30 CHF 30 37797 900004191 2 -510 CHF -480 37797 900004191 3 -100 CHF -580 37797 900004191 4 429 CHF -151 37797 900004191 5 84 CHF -67 37797 900004191 6 30 CHF -37 37797 900004191 7 -109 CHF -146 37797 900004191 8 -20 CHF -166 37797 900004191 9 24 CHF -142 37797 900004191 10 4 CHF -138 37797 900004191 11 30 CHF -108 37797 900004191 12 -439 CHF -547 37797 900004191 13 -81 CHF -628 37797 900004191 14 354 CHF -274 37797 900004191 15 65 CHF -209 37797 900004191 16 30 CHF -179 37797 900004191 17 -332 CHF -511 37797 900004191 18 -74 CHF -585 37797 900004191 19 260 CHF -325 37797 900004191 20 58 CHF -267 37797 900005419 1 60 CHF 60 37797 900005419 2 20 CHF 80 37797 900010226 1 30 CHF 30 37797 1000010315 1 50 CHF 50 37797 1000010315 2 100 CHF 150 37797 1000010315 3 600 CHF 750 37797 1000010315 4 480 CHF 1230 37797 1000010315 5 100 CHF 1330 37797 1000010315 6 540 CHF 1870 37797 1000010315 7 50 CHF 1920 37797 1000010315 8 60 CHF 1980 37797 1000010315 9 80 CHF 2060 37797 1000010315 10 100 CHF 2160 37797 1000010315 11 690 CHF 2850 37797 1000010315 12 50 CHF 2900 37797 1000010315 13 100 CHF 3000 37797 1000010315 14 690 CHF 3690 37797 1000010315 15 80 CHF 3770 37797 1000010315 16 60 CHF 3830 37797 1000010315 17 60 CHF 3890 37797 1000042245 1 34 CHF 34 37797 1000042245 2 -50 CHF -16 37797 1000042245 3 34 CHF 18 37797 1000042245 4 -50 CHF -32 37797 1000042245 5 34 CHF 2 37797 1000042245 6 -50 CHF -48 37797 1000042245 7 34 CHF -14 37797 1000042245 8 -50 CHF -64 37797 1000042245 9 34 CHF -30 37797 1000042245 10 -50 CHF -80 37797 1000042245 11 194 CHF 114 37797 1000042245 12 -284 CHF -170 37797 1000042245 13 34 CHF -136 37797 1000042245 14 -50 CHF -186 37797 1000042245 15 34 CHF -152 37797 1000042245 16 -50 CHF -202 37797 1000042245 17 34 CHF -168 37797 1000042245 18 -50 CHF -218 37797 1000042245 19 34 CHF -184 37797 1000042245 20 -50 CHF -234 37797 1000042245 21 34 CHF -200 37797 1000042245 22 -50 CHF -250 37797 1000042245 23 34 CHF -216 37797 1000042245 24 -50 CHF -266 37797 1000042245 25 34 CHF -232 37797 1000042245 26 -50 CHF -282 37797 1000042245 27 34 CHF -248 37797 1000042245 28 -50 CHF -298 37797 1000042245 29 34 CHF -264 37797 1000042245 30 -50 CHF -314 37797 1000042245 31 34 CHF -280 37797 1000042245 32 -50 CHF -330 37797 1000042245 33 -50 CHF -380 37797 1000042245 34 34 CHF -346 37797 1000042245 35 34 CHF -312 37797 1000042245 36 -50 CHF -362 37797 1000042245 37 34 CHF -328 37797 1000042245 38 -50 CHF -378 37797 1000042245 39 34 CHF -344 37797 1000042245 40 -50 CHF -394 37797 1000042245 41 34 CHF -360 37797 1000042245 42 -50 CHF -410 37797 1000042245 43 34 CHF -376 37797 1000042245 44 -50 CHF -426 37797 1000042245 45 34 CHF -392 37797 1000042245 46 -50 CHF -442 37797 1000042245 47 34 CHF -408 37797 1000042245 48 -50 CHF -458 37797 1000042245 49 34 CHF -424 37797 1000042245 50 -50 CHF -474 37797 1000042245 51 34 CHF -440 37797 1000042245 52 -50 CHF -490 37797 1000042245 53 34 CHF -456 37797 1000042245 54 -50 CHF -506 37797 1000046160 1 50 CHF 50 37797 1000046160 2 100 CHF 150 37797 1000046160 3 600 CHF 750 37797 1000046160 4 480 CHF 1230 37797 1000046160 5 100 CHF 1330 37797 1000046160 6 540 CHF 1870 37797 1000046160 7 50 CHF 1920 37797 1000046160 8 60 CHF 1980 37797 1000046160 9 80 CHF 2060 37797 1000046160 10 100 CHF 2160 37797 1000046160 11 690 CHF 2850 37797 1000046160 12 50 CHF 2900 37797 1000046160 13 100 CHF 3000 37797 1000046160 14 690 CHF 3690 37797 1000046160 15 80 CHF 3770 37797 1000046160 16 60 CHF 3830 37797 1000046160 17 60 CHF 3890 37797 1000048293 1 50 CHF 50 37797 1000053891 1 50 CHF 50 37797 1000053891 2 100 CHF 150 37797 1000053891 3 600 CHF 750 37797 1000053891 4 480 CHF 1230 37797 1000053891 5 100 CHF 1330 37797 1000053891 6 540 CHF 1870 37797 1000053891 7 50 CHF 1920 37797 1000053891 8 60 CHF 1980 37797 1000053891 9 80 CHF 2060 37797 1000053891 10 100 CHF 2160 37797 1000053891 11 690 CHF 2850 37797 1000053891 12 50 CHF 2900 37797 1000053891 13 100 CHF 3000 37797 1000053891 14 690 CHF 3690 37797 1000053891 15 80 CHF 3770 37797 1000053891 16 60 CHF 3830 37797 1000053891 17 60 CHF 3890 37797 1000054727 1 50 CHF 50 37797 1000054727 2 100 CHF 150 37797 1000054727 3 600 CHF 750 37797 1000054727 4 480 CHF 1230 37797 1000054727 5 100 CHF 1330 37797 1000054727 6 540 CHF 1870 37797 1000054727 7 50 CHF 1920 37797 1000054727 8 60 CHF 1980 37797 1000054727 9 80 CHF 2060 37797 1000054727 10 100 CHF 2160 37797 1000054727 11 690 CHF 2850 37797 1000054727 12 50 CHF 2900 37797 1000054727 13 100 CHF 3000 37797 1000054727 14 690 CHF 3690 37797 1000054727 15 80 CHF 3770 37797 1000054727 16 60 CHF 3830 37797 1000054727 17 60 CHF 3890 37797 1000054727 18 50 CHF 3940 37797 1000054727 19 419 CHF 4359 37797 1000054727 20 78 CHF 4437 37797 1000054882 1 -60 CHF -60 37797 1000054882 2 -60 CHF -120 37797 1000054882 3 14 CHF -106 37797 1000054882 4 14 CHF -92 37797 1000054882 5 38 CHF -54 37797 1000054882 6 -50 CHF -104 37797 1000054882 7 46 CHF -58 37797 1000054882 8 11 CHF -47 37797 1000054882 9 50 CHF 3 37797 1000054882 10 -38 CHF -35 37797 1000054882 11 59 CHF 24 37797 1000054882 12 50 CHF 74 37797 1000054882 13 -50 CHF 24 37797 1000054882 14 11 CHF 35 37797 1000055210 1 77 CHF 77 37797 1000055210 2 394 CHF 471 37797 1000058039 1 417 CHF 417 37797 1000058039 2 73 CHF 490 37797 1000058039 3 73 CHF 563 37797 1000058039 4 73 CHF 636 37797 1000058039 5 73 CHF 709 37797 1000058039 6 73 CHF 782 37797 1000058039 7 73 CHF 855 37797 1000058039 8 73 CHF 928 37797 1000058039 9 73 CHF 1001 37797 1000058039 10 73 CHF 1074 37797 1000058039 11 73 CHF 1147 37797 1000058039 12 73 CHF 1220 37797 1000058039 13 73 CHF 1293 37797 1000058039 14 73 CHF 1366 37797 1000058039 15 73 CHF 1439 37797 1000058039 16 73 CHF 1512 37797 1000058039 17 73 CHF 1585 37797 1000058039 18 73 CHF 1658 37797 1000058039 19 73 CHF 1731 37797 1000058039 20 73 CHF 1804 37797 1000058039 21 73 CHF 1877 37797 1000058039 22 73 CHF 1950 37797 1000058039 23 73 CHF 2023 37797 1000058039 24 73 CHF 2096 37797 1000058039 25 73 CHF 2169 37797 1000058039 26 73 CHF 2242 37797 1000058039 27 73 CHF 2315 37797 1000062802 1 60 CHF 60 37797 1000064602 1 240 CHF 240 37797 1000064602 2 -240 CHF 0 37797 1000064602 3 300 CHF 300 37797 1000064602 4 67 CHF 367 37797 1000064602 5 50 CHF 417 37797 1000065941 1 73 CHF 73 37797 1000066157 1 77 CHF 77 37797 1000066157 2 394 CHF 471 37797 1000066685 1 25 CHF 25 37797 1000069908 1 20 CHF 20 37797 1000070001 1 417 CHF 417 37797 1000070001 2 73 CHF 490 37797 1000070001 3 73 CHF 563 37797 1000070001 4 73 CHF 636 37797 1000070001 5 73 CHF 709 37797 1000070001 6 73 CHF 782 37797 1000070001 7 73 CHF 855 37797 1000070001 8 73 CHF 928 37797 1000070001 9 73 CHF 1001 37797 1000070001 10 73 CHF 1074 37797 1000070001 11 73 CHF 1147 37797 1000070001 12 73 CHF 1220 37797 1000070001 13 73 CHF 1293 37797 1000070001 14 73 CHF 1366 37797 1000070001 15 73 CHF 1439 37797 1000070001 16 73 CHF 1512 37797 1000070001 17 73 CHF 1585 37797 1000070001 18 73 CHF 1658 37797 1000070001 19 73 CHF 1731 37797 1000070001 20 73 CHF 1804 37797 1000070001 21 73 CHF 1877 37797 1000070001 22 73 CHF 1950 37797 1000070001 23 73 CHF 2023 37797 1000070001 24 73 CHF 2096 37797 1000070001 25 73 CHF 2169 37797 1000070001 26 73 CHF 2242 37797 1000070001 27 73 CHF 2315