Percentile

Hi, l would like to calculate 80% and 95% percentile of a column from my table in SQL server 2005. Does SQL server 2005 provides such built-in function, if not, how should l do? Any suggestion? (Suggest to MS : Please include percentile as a built-in function on the next services pack). Thanks and happy labor day!

Lookup the NTILE and RANK functions in Books Online.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

|||

Can you provide more info about what percentage you want to calculate and what values you have available?

|||

Hi,

Thanks for the reply.

Currently l've a table with the following table structure, i.e. Region, School Name and Scope. l am interested to know the 95% and 80% percentile of the Scope in each region. How should l do?

|||

Without DDL and sample data this is difficult to answer, however
this may help

select Region,
[School Name],
Scope
from (select Region,
[School Name],
Scope,
NTILE(100) OVER(PARTITION BY Region ORDER BY Scope) AS NTile100
from RegionTable) X
where NTile100 >= 95

|||

Hi, Mark

l've a table called RESULT with the structure, i.e.

Region VARCHAR(50)

School Name VARCHAR(50)

Score FLOAT

With Sample Data

Region School Score

A X 75

A X 90

A X 85

A Y 60

A Y 30

B W 90

........................

So l would interest to know the 95% & 85% score for each region. At the end, for each percentile, l should have one score corresponding to it, i.e For region A, my 95% percentile is 80 and 80% oercentile is 70.

By using your method, l will have a few record with percentile 95%.....so l wonder which one should l pick?

Again, thanks a lot for your help

|||

Hi,

Currently l'm adopt the following solution to the problem, i.e.

SELECT region,

(SELECT MAX(score)

FROM (

SELECT TOP(95) score FROM Result

WHERE region = s.region

ORDER BY score

) As Tmp) as score95

FROM Result s

GROUP BY region

l am looking for something simple to find out the score represent 95% percentile.......someone mentions to me that by it can be done by using NTILE(100)....but l dun have any idea how do to that.