Re: percentile rank query

On Thu, Apr 10, 2008 at 05:20:21PM +0100, William Temperley wrote:
> SELECT count(*) AS frequency, score,
> ((((SELECT count(uid) FROM scoretable st2 WHERE st2.score <=
> st1.score) - count(*)) + (count(*)/2))::float/(select
> count(*) from scoretable))
>
> FROM scoretable st1
> GROUP BY score
> ORDER BY score
>
> I think that's a percentile rank now.

I'm not quite sure how this is calculated but I think you may want to
be converting to a non-integral type earlier (i.e. as you're dividing
by two, not after).  I also find all the subselects a bit difficult to
follow so have moved them around:

  SELECT x.frequency, x.score, (x.rank + x.frequency / 2.0) / y.total AS pr
  FROM (
    SELECT count(*) AS frequency, score,
      (SELECT count(*) FROM scoretable t WHERE t.score < s.score) AS rank
    FROM scoretable s
    GROUP BY score) x, (SELECT count(*) AS total FROM scoretable) y
  ORDER BY score;


  Sam