The Oracle Sum fuction is very useful. Also analytical Oracle Sum functions are available. Using sum(
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 🔗
PERSNRBILLNRPOSITIONNRBETRAGCURRENCYORACLE_SUM37797900004191130CHF30377979000041912-510CHF-480377979000041913-100CHF-580377979000041914429CHF-15137797900004191584CHF-6737797900004191630CHF-37377979000041917-109CHF-146377979000041918-20CHF-16637797900004191924CHF-14237797900004191104CHF-138377979000041911130CHF-1083779790000419112-439CHF-5473779790000419113-81CHF-6283779790000419114354CHF-274377979000041911565CHF-209377979000041911630CHF-1793779790000419117-332CHF-5113779790000419118-74CHF-5853779790000419119260CHF-325377979000041912058CHF-26737797900005419160CHF6037797900005419220CHF8037797900010226130CHF30377971000010315150CHF503779710000103152100CHF1503779710000103153600CHF7503779710000103154480CHF12303779710000103155100CHF13303779710000103156540CHF1870377971000010315750CHF1920377971000010315860CHF1980377971000010315980CHF206037797100001031510100CHF216037797100001031511690CHF28503779710000103151250CHF290037797100001031513100CHF300037797100001031514690CHF36903779710000103151580CHF37703779710000103151660CHF38303779710000103151760CHF3890377971000042245134CHF343779710000422452-50CHF-16377971000042245334CHF183779710000422454-50CHF-32377971000042245534CHF23779710000422456-50CHF-48377971000042245734CHF-143779710000422458-50CHF-64377971000042245934CHF-3037797100004224510-50CHF-8037797100004224511194CHF11437797100004224512-284CHF-1703779710000422451334CHF-13637797100004224514-50CHF-1863779710000422451534CHF-15237797100004224516-50CHF-2023779710000422451734CHF-16837797100004224518-50CHF-2183779710000422451934CHF-18437797100004224520-50CHF-2343779710000422452134CHF-20037797100004224522-50CHF-2503779710000422452334CHF-21637797100004224524-50CHF-2663779710000422452534CHF-23237797100004224526-50CHF-2823779710000422452734CHF-24837797100004224528-50CHF-2983779710000422452934CHF-26437797100004224530-50CHF-3143779710000422453134CHF-28037797100004224532-50CHF-33037797100004224533-50CHF-3803779710000422453434CHF-3463779710000422453534CHF-31237797100004224536-50CHF-3623779710000422453734CHF-32837797100004224538-50CHF-3783779710000422453934CHF-34437797100004224540-50CHF-3943779710000422454134CHF-36037797100004224542-50CHF-4103779710000422454334CHF-37637797100004224544-50CHF-4263779710000422454534CHF-39237797100004224546-50CHF-4423779710000422454734CHF-40837797100004224548-50CHF-4583779710000422454934CHF-42437797100004224550-50CHF-4743779710000422455134CHF-44037797100004224552-50CHF-4903779710000422455334CHF-45637797100004224554-50CHF-506377971000046160150CHF503779710000461602100CHF1503779710000461603600CHF7503779710000461604480CHF12303779710000461605100CHF13303779710000461606540CHF1870377971000046160750CHF1920377971000046160860CHF1980377971000046160980CHF206037797100004616010100CHF216037797100004616011690CHF28503779710000461601250CHF290037797100004616013100CHF300037797100004616014690CHF36903779710000461601580CHF37703779710000461601660CHF38303779710000461601760CHF3890377971000048293150CHF50377971000053891150CHF503779710000538912100CHF1503779710000538913600CHF7503779710000538914480CHF12303779710000538915100CHF13303779710000538916540CHF1870377971000053891750CHF1920377971000053891860CHF1980377971000053891980CHF206037797100005389110100CHF216037797100005389111690CHF28503779710000538911250CHF290037797100005389113100CHF300037797100005389114690CHF36903779710000538911580CHF37703779710000538911660CHF38303779710000538911760CHF3890377971000054727150CHF503779710000547272100CHF1503779710000547273600CHF7503779710000547274480CHF12303779710000547275100CHF13303779710000547276540CHF1870377971000054727750CHF1920377971000054727860CHF1980377971000054727980CHF206037797100005472710100CHF216037797100005472711690CHF28503779710000547271250CHF290037797100005472713100CHF300037797100005472714690CHF36903779710000547271580CHF37703779710000547271660CHF38303779710000547271760CHF38903779710000547271850CHF394037797100005472719419CHF43593779710000547272078CHF44373779710000548821-60CHF-603779710000548822-60CHF-120377971000054882314CHF-106377971000054882414CHF-92377971000054882538CHF-543779710000548826-50CHF-104377971000054882746CHF-58377971000054882811CHF-47377971000054882950CHF337797100005488210-38CHF-353779710000548821159CHF243779710000548821250CHF7437797100005488213-50CHF243779710000548821411CHF35377971000055210177CHF773779710000552102394CHF4713779710000580391417CHF417377971000058039273CHF490377971000058039373CHF563377971000058039473CHF636377971000058039573CHF709377971000058039673CHF782377971000058039773CHF855377971000058039873CHF928377971000058039973CHF10013779710000580391073CHF10743779710000580391173CHF11473779710000580391273CHF12203779710000580391373CHF12933779710000580391473CHF13663779710000580391573CHF14393779710000580391673CHF15123779710000580391773CHF15853779710000580391873CHF16583779710000580391973CHF17313779710000580392073CHF18043779710000580392173CHF18773779710000580392273CHF19503779710000580392373CHF20233779710000580392473CHF20963779710000580392573CHF21693779710000580392673CHF22423779710000580392773CHF2315377971000062802160CHF603779710000646021240CHF2403779710000646022-240CHF03779710000646023300CHF300377971000064602467CHF367377971000064602550CHF417377971000065941173CHF73377971000066157177CHF773779710000661572394CHF471377971000066685125CHF25377971000069908120CHF203779710000700011417CHF417377971000070001273CHF490377971000070001373CHF563377971000070001473CHF636377971000070001573CHF709377971000070001673CHF782377971000070001773CHF855377971000070001873CHF928377971000070001973CHF10013779710000700011073CHF10743779710000700011173CHF11473779710000700011273CHF12203779710000700011373CHF12933779710000700011473CHF13663779710000700011573CHF14393779710000700011673CHF15123779710000700011773CHF15853779710000700011873CHF16583779710000700011973CHF17313779710000700012073CHF18043779710000700012173CHF18773779710000700012273CHF19503779710000700012373CHF20233779710000700012473CHF20963779710000700012573CHF21693779710000700012673CHF22423779710000700012773CHF2315