Search Postgresql Archives

percentile rank query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all

I'm trying to calculate the percentile rank for a record based on a
'score' column, e.g. a column of integers such as:
23,77,88,23,23,23,12,12,12,13,13,13
without using a stored procedure.

So,
select count(*) as frequency, score
from scoretable
group by score
order by score

Yields:

frequency score
3             12
3             13
4             23
1             77
1              88


However I'd like this result set:

frequency score   runningtotal
3             12        3
3             13        6
4             23        10
1             77        11
1              88       12

Where the running total is the previous frequency added to the current
frequency. Score order is significant.

So I can then do ((runningtotal-frequency)+(frequency/2))/(select
count(*) from scoretable) to give me the percentile rank for each
score.

Is this possible in one query? I just can't figure out how to get the
running total in a result set.

Thanks very much,

Will Temperley


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux