Percentile Rank - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Percentile Rank

Author

Topic

udayfn12
Starting Member

15 Posts

Posted - 2002-09-18 : 11:42:01

Hi All,

I am trying to find percentiles. I found the solution from this forum but the results have some inconsistencies when the data items have same values. This application is a migration project and my results are very close to existing application which is written in SAS / Cobol.

The current solution I am using is

select student , math,
(( select count(*) from examresults b where a.math > b.math) + ((select count(*) from examresults c where a.math = c.math) * .5))/ (select count(*) from examresults) * 100 as percentile
from examresults a

Output
-------

student math percentile
---------- ----------- ------------------------------
Lenny 2 25.000000000000
Ralph 3 35.000000000000
Joe 4 45.000000000000
Mary 5 65.000000000000
Frank 5 65.000000000000
Susan 5 65.000000000000
Bill 7 85.000000000000
Ben 0 10.000000000000
Fred 0 10.000000000000
George 10 95.000000000000

Output of the existing system

27
36
45
63
63
63
81
13
13
90

The existing system is calculating percentile Rank using the formula

100 * Rank / (Number of Defined Values + 1)

Where Rank = the ratio's position after sorting all values from smallest (position = 1) to largest (position = the number of defined values)

Note: The rank is a financial median when more than two rows has same value. (This is my problem (calculating median), if I use this formula).

My table has around 50,000 rows and I have to calculate percentiles on 55 columns on each row.

I would be grateful if any one could give me best solution.

Thanks,
Reddy.

Please find the DDL statements

create table ExamResults (Name varchar(10), math int);

insert into ExamResults values ('Lenny',2);
insert into ExamResults values ('Ralph',3);
insert into ExamResults values ('Joe',4);
insert into ExamResults values ('Mary',5);
insert into ExamResults values ('Frank',5);
insert into ExamResults values ('Susan',5);
insert into ExamResults values ('Bill',7);
insert into ExamResults values ('Ben',0);
insert into ExamResults values ('Fred',0);
insert into ExamResults values ('George',10);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources