-- The fifth index (level) is set to NULL. Since the fifth value is NULL, -- the total remains the same as previous value, same with the average. SELECT q_tab.ref_no, TO_NUMBER(q_tab.val) val, SUM(q_tab.val) OVER(ORDER BY q_tab.ref_no) cumm_totals, ROUND(AVG(q_tab.val) OVER(ORDER BY q_tab.ref_no),4) avg_val, ROUND((SUM(q_tab.val) OVER(ORDER BY q_tab.ref_no))/ROWNUM,4) cumm_Avg FROM (SELECT level ref_no, DECODE(level,5,NULL,level) val FROM DUAL CONNECT BY level <=10 ) q_tab; REF_NO VAL CUMM_TOTALS AVG_VAL CUMM_AVG ---------- ----------- ----------- ---------- ---------- 1 1 1 1 1 2 2 3 1.5 1.5 3 3 6 2 2 4 4 10 2.5 2.5 5 10 2.5 2 6 6 16 3.2 2.6667 7 7 23 3.8333 3.2857 8 8 31 4.4286 3.875 9 9 40 5 4.4444 10 10 50 5.5556 5 10 rows selected. -- Note: The AVG function results in (1/1, 2/2, 3/3, etc.) for each -- row and the fifth value will be null. The data has to be -- grouped (GROUP BY). SELECT q_tab.ref_no, TO_NUMBER(q_tab.val) val, SUM(q_tab.val) OVER(ORDER BY q_tab.ref_no) cumm_totals, ROUND(AVG(q_tab.val) OVER(ORDER BY q_tab.ref_no),4) avg_val, ROUND((SUM(q_tab.val) OVER(ORDER BY q_tab.ref_no))/ROWNUM,4) cumm_Avg, AVG(q_tab.val) avg_fn FROM (SELECT level ref_no, DECODE(level,5,NULL,level) val FROM DUAL CONNECT BY level <=10 ) q_tab GROUP BY q_tab.ref_no, q_tab.val, ROWNUM ; REF_NO VAL CUMM_TOTALS AVG_VAL CUMM_AVG AVG_FN ---------- ----------- ----------- ---------- ---------- ---------- 1 1 1 1 1 1 2 2 3 1.5 1.5 2 3 3 6 2 2 3 4 4 10 2.5 2.5 4 5 10 2.5 2 6 6 16 3.2 2.6667 6 7 7 23 3.8333 3.2857 7 8 8 31 4.4286 3.875 8 9 9 40 5 4.4444 9 10 10 50 5.5556 5 10 10 rows selected.
55793