Possible to Count the Cumulative Percentage by SQL?

UDA
Enthusiast

Possible to Count the Cumulative Percentage by SQL?

Dear who can solve the puzzle,
Just want to confirm on this.
By giving the example below, let say I had a sample of 50 records, and I accumulate the records ascending(see the column Accum_Value).
The 1st row=7
The 2nd row=7+16=23
The 3rd row=7+16+12=35
. . .
Last row = 7+16+12. . .+2+1=50
____________________________________________________
Records...Accum_Value......Accum_Prct.......Formula
....7.................7.....................14%.............=(7/50)*100
...16...............23.....................46%.............=(23/50)*100
...12...............35.....................70%.............=(35/50)*100
....1...............36.....................72%.............=(36/50)*100
....2...............38.....................76%.............=(38/50)*100
....4...............42.....................84%.............=(42/50)*100
....2...............44.....................88%.............=(44/50)*100
....1...............45.....................90%.............=(45/50)*100
....2...............47.....................94%.............=(47/50)*100
....2...............49.....................98%.............=(49/50)*100
....1...............50.....................100%............=(50/50)*100
_____________________________________________________
Does SQL can perform the calculation of the Accumulative Percentage above (Column 'Accum_Prct') by using the formula above?
Many Thanks.
2 REPLIES
Senior Apprentice

Re: Possible to Count the Cumulative Percentage by SQL?

Hi Benjamin,
easy using OLAP functions:

select
records,
sum(records) over (order by ???
rows unbounded preceding) as Accum_Value,
100 * Accum_Value / sum(records) over ()
from tab

Dieter
Enthusiast

Re: Possible to Count the Cumulative Percentage by SQL?

Dear Dieter,
You are genius. Many thanks. :-)