Total oracle sum

Oracle cumulative sum // Add the values of a column in the current row to the values of the column in the previous row, that is, the cumulative sum: // Method 1: with t as (select 1 val from dual union all select 3 from dual union all select 5 from dual union all select 7 from dual union all select 9 from dual) select val, sum (val) over (order by rownum rows between unbounded preceding and current row) sum_val from t group by rownum, val order by rownum; VAL SUM_VAL ---------- 1 1 3 4 5 9 7 16 9 25 // resolution: // sum (val) calculates the sum; // order by rownum sorts query records by pseudo-column rownum; // between unbounded preceding and current row: defines the start and end points of the window; // unbounded preceding: the start point of the window includes all rows read. // current row: The end of the window is the current row. The default value can be omitted. /// Method 2: with cte_1 as (select 1 val from dual union all select 3 from dual union all select 5 from dual union all select 7 from dual union all select 9 from dual ), cte_2 as (select rownum rn, val from cte_1) select. val, sum (B. val) sum_val from cte_2 a, cte_2 B where B. rn <=. rn group by. val // method 3: // create a recursive function, sum // f (n) = x + f (n-1) create table t as select 1 id, 1 val from dual union all select 2 and 3 from dual union all select 3, 5 from dual union all select 4, 7 from dual union all select 5, 9 from dual/create or replace function fun_recursion (x in int) return integer is n integer: = 0; begin select val into n from t where id = x; if x = 1 then return n; else return n + fun_recursion (x-1 ); end if; exception when others then dbms_output.put_line (sqlerrm); end fun_recursion;/select val, fun_recursion (id) sum_val from t; VAL SUM_VAL ---------- 1 1 3 4 5 9 7 16 9 25 //