Wednesday, August 15, 2012

Calculate cumulative percentage in Oracle

Using Cume_dist(), it is easy to calculate the cumulative percentage of a number.

1. The data.
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2. Calculate cumulative percentage using cums_dist()
select num, cume_dist() over(order by num) as cum_percent from TBL_TEST order by num;
       NUM CUM_PERCENT
---------- -----------
         1  .222222222
         1  .222222222
         2  .555555556
         2  .555555556
         2  .555555556
         3  .666666667
         4  .888888889
         4  .888888889
         5           1

No comments: