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
