Cummulative Total With Null A Value
-- 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.


Cummulative Totals

Yearly Totals

Cummulative SUM and Totals Analysis

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 10th, 2013

  440