<p>What's the best way to calculate percentile rankings (e.g. the 90th percentile or the median score) in MSSQL 2005?</p>

<p>I'd like to be able to select the 25th, median, and 75th percentiles for a single column of scores (preferably in a single record so I can combine with average, max, and min).  So for example, table output of the results might be:</p>

<pre><code>Group  MinScore  MaxScore  AvgScore  pct25  median  pct75
-----  --------  --------  --------  -----  ------  -----
T1     52        96        74        68     76      84
T2     48        98        74        68     75      85
</code></pre>

[{'answer_id': 79758, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>i'd probably use a the sql server 2005  </p>\n\n<blockquote>\n  <p>row_number() over (order by score ) / (select count(*) from scores)</p>\n</blockquote>\n\n<p>or something along those lines. </p>\n"}, {'answer_id': 79766, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>i'd do something like:</p>\n\n<pre><code>select @n = count(*) from tbl1\nselect @median = @n / 2\nselect @p75 = @n * 3 / 4\nselect @p90 = @n * 9 / 10\n\nselect top 1 score from (select top @median score from tbl1 order by score asc) order by score desc\n</code></pre>\n\n<p>is this right?</p>\n"}, {'answer_id': 79990, 'author': 'Matt', 'author_id': 4154, 'author_profile': 'https://Stackoverflow.com/users/4154', 'pm_score': 4, 'selected': False, 'text': '<p>I would think that this would be the simplest solution:</p>\n\n<pre><code>SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC\n</code></pre>\n\n<p>Where N = (100 - desired percentile).  So if you wanted all rows in the 90th percentile, you\'d select the top 10%.</p>\n\n<p>I\'m not sure what you mean by "preferably in a single record".  Do you mean calculate which percentile a given score for a single record would fall into?  e.g. do you want to be able to make statements like "your score is 83, which puts you in the 91st percentile." ?</p>\n\n<p>EDIT: OK, I thought some more about your question and came up with this interpretation.  Are you asking how to calculate the cutoff score for a particular percentile?  e.g. something like this: to be in the 90th percentile you must have a score greater than 78.</p>\n\n<p>If so, this query works.  I dislike sub-queries though, so depending on what it was for, I\'d probably try to find a more elegant solution.  It does, however, return a single record with a single score.</p>\n\n<pre><code>-- Find the minimum score for all scores in the 90th percentile\nSELECT Min(subq.TheScore) FROM\n(SELECT TOP 10 PERCENT TheScore FROM TheTable\nORDER BY TheScore DESC) AS subq\n</code></pre>\n'}, {'answer_id': 342502, 'author': 'Soldarnal', 'author_id': 3420, 'author_profile': 'https://Stackoverflow.com/users/3420', 'pm_score': 1, 'selected': False, 'text': "<p>I've been working on this a little more, and here's what I've come up with so far:</p>\n\n<pre><code>CREATE PROCEDURE [dbo].[TestGetPercentile]\n\n@percentile as float,\n@resultval as float output\n\nAS\n\nBEGIN\n\nWITH scores(score, prev_rank, curr_rank, next_rank) AS (\n    SELECT dblScore,\n        (ROW_NUMBER() OVER ( ORDER BY dblScore ) - 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [prev_rank],\n        (ROW_NUMBER() OVER ( ORDER BY dblScore ) + 0.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [curr_rank],\n        (ROW_NUMBER() OVER ( ORDER BY dblScore ) + 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1)  [next_rank]\n    FROM TestScores\n)\n\nSELECT @resultval = (\n    SELECT TOP 1 \n    CASE WHEN t1.score = t2.score\n        THEN t1.score\n    ELSE\n        t1.score + (t2.score - t1.score) * ((@percentile - t1.curr_rank) / (t2.curr_rank - t1.curr_rank))\n    END\n    FROM scores t1, scores t2\n    WHERE (t1.curr_rank = @percentile OR (t1.curr_rank &lt; @percentile AND t1.next_rank &gt; @percentile))\n        AND (t2.curr_rank = @percentile OR (t2.curr_rank &gt; @percentile AND t2.prev_rank &lt; @percentile))\n)\n\nEND\n</code></pre>\n\n<p>Then in another stored procedure I do this:</p>\n\n<pre><code>DECLARE @pct25 float;\nDECLARE @pct50 float;\nDECLARE @pct75 float;\n\nexec SurveyGetPercentile .25, @pct25 output\nexec SurveyGetPercentile .50, @pct50 output\nexec SurveyGetPercentile .75, @pct75 output\n\nSelect\n    min(dblScore) as minScore,\n    max(dblScore) as maxScore,\n    avg(dblScore) as avgScore,\n    @pct25 as percentile25,\n    @pct50 as percentile50,\n    @pct75 as percentile75\nFrom TestScores\n</code></pre>\n\n<p>It still doesn't do quite what I'm looking for.  This will get the stats for all tests; whereas I would like to be able to select from a TestScores table that has multiple different tests in it and get back the same stats for each different test (like I have in my example table in my question).</p>\n"}, {'answer_id': 6504968, 'author': 'Kay Aliu', 'author_id': 818956, 'author_profile': 'https://Stackoverflow.com/users/818956', 'pm_score': 1, 'selected': False, 'text': '<p>The 50th percentile is same as the median. When computing other percentile, say the 80th, sort the data for the 80 percent of data in ascending order and the other 20 percent in descending order, and take the avg of the two middle value.</p>\n\n<p>NB: The median query has been around for a long time, but cannot remember where exactly I got it from, I have only amended it to compute other percentiles.</p>\n\n<pre><code>DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))\n\nINSERT INTO @Temp VALUES(0)\nINSERT INTO @Temp VALUES(2)\nINSERT INTO @Temp VALUES(8)\nINSERT INTO @Temp VALUES(4)\nINSERT INTO @Temp VALUES(3)\nINSERT INTO @Temp VALUES(6)\nINSERT INTO @Temp VALUES(6)\nINSERT INTO @Temp VALUES(6) \nINSERT INTO @Temp VALUES(7)\nINSERT INTO @Temp VALUES(0)\nINSERT INTO @Temp VALUES(1)\nINSERT INTO @Temp VALUES(NULL)\n\n\n--50th percentile or median\nSELECT ((\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 50 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA\n                ) AS A\n        ORDER BY DATA DESC) + \n        (\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 50 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA DESC\n                ) AS A\n        ORDER BY DATA ASC)) / 2.0\n\n\n--90th percentile \nSELECT ((\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 90 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA\n                ) AS A\n        ORDER BY DATA DESC) + \n        (\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 10 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA DESC\n                ) AS A\n        ORDER BY DATA ASC)) / 2.0\n\n\n--75th percentile\nSELECT ((\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 75 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA\n                ) AS A\n        ORDER BY DATA DESC) + \n        (\n        SELECT TOP 1 DATA\n        FROM   (\n                SELECT  TOP 25 PERCENT DATA\n                FROM    @Temp\n                WHERE   DATA IS NOT NULL\n                ORDER BY DATA DESC\n                ) AS A\n        ORDER BY DATA ASC)) / 2.0\n</code></pre>\n'}, {'answer_id': 6512841, 'author': 'Elizabeth', 'author_id': 819953, 'author_profile': 'https://Stackoverflow.com/users/819953', 'pm_score': 3, 'selected': False, 'text': '<p>Check out the NTILE command -- it will give you percentiles pretty easily!</p>\n\n<pre><code>SELECT  SalesOrderID, \n    OrderQty,\n    RowNum = Row_Number() OVER(Order By OrderQty),\n    Rnk = RANK() OVER(ORDER BY OrderQty),\n    DenseRnk = DENSE_RANK() OVER(ORDER BY OrderQty),\n    NTile4  = NTILE(4) OVER(ORDER BY OrderQty)\nFROM Sales.SalesOrderDetail \nWHERE SalesOrderID IN (43689, 63181)\n</code></pre>\n'}, {'answer_id': 12291090, 'author': 'Paul', 'author_id': 1650451, 'author_profile': 'https://Stackoverflow.com/users/1650451', 'pm_score': 2, 'selected': False, 'text': '<p>How about this:</p>\n\n<pre><code>SELECT\n  Group,\n  75_percentile =  MAX(case when NTILE(4) OVER(ORDER BY score ASC) = 3 then score  else 0 end),\n  90_percentile =  MAX(case when NTILE(10) OVER(ORDER BY score  ASC) = 9 then score  else 0 end)     \nFROM TheScore\nGROUP BY Group\n</code></pre>\n'}, {'answer_id': 55066450, 'author': 'Debasmita', 'author_id': 7025084, 'author_profile': 'https://Stackoverflow.com/users/7025084', 'pm_score': 0, 'selected': False, 'text': '<p>Percentile is calculated by </p>\n\n<p><code>(Rank -1) /(total_rows -1)</code> when you sort values in ascending order. </p>\n\n<p>The below query will give you percentile value between 0 and 1. Person with lowest marks will have 0 percentile.</p>\n\n<pre><code>SELECT Name, marks, (rank_1-1)/((select count(*) as total_1 from table)-1)as percentile_rank\nfrom\n(\nSELECT Name,\n       Marks,\n       RANK() OVER (ORDER BY Marks) AS rank_1\n       from table\n) as A\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/79688', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3420/']