Re: percentile rank query

William Temperley escreveu:
> 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.
>


Try:

SELECT count(*) AS frequency, score,
count((SELECT * FROM scoretable st2 WHERE st2.score <= st1.score)) AS
runningtotal
FROM scoretable st1
GROUP BY score
ORDER BY score

Osvaldo