Trying to find a PERCENTILE values

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)

Trying to find a PERCENTILE values

Author

Topic

udayfn12
Starting Member

15 Posts

Posted - 2002-07-30 : 11:39:46

Hi,

We are trying to find an effecient (fast) way of calculating the percentile ranking of numerical data.

Currently we do not have the option of using SQL 2000
Analysis Services.

Here is table and data looks like this.
create table ExamResults
(Student varchar(15),
Math int,
Science int,
Social int,
English int);

insert into ExamResults values ('Lenny',94,83,82,94);
insert into ExamResults values ('Ralph',93,62,78,90);
insert into ExamResults values ('Joe',91,72,89,92);
insert into ExamResults values ('Mary',80,56,78,65);
insert into ExamResults values ('Frank',89,80,99,98);
insert into ExamResults values ('Susan',89,78,62,99);
insert into ExamResults values ('Bill',74,88,82,87);
insert into ExamResults values ('Ben',99,97,96,95);
insert into ExamResults values ('Fred',66,67,68,69);
insert into ExamResults values ('George',100,99,63,78);
insert into ExamResults values ('Walter',90,55,95,86);
insert into ExamResults values ('Betty',89,62,72,82);
insert into ExamResults values ('Heather',83,73,83,92);
insert into ExamResults values ('Richard',91,78,82,84);

I want to calculate percentile values for Math, Science,Social and English.

Any help is greatly appreciated.

Thanks,
Reddy.

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-30 : 22:16:39

This should get you started...

SELECT Math,
(SELECT COUNT(*) FROM ExamResults b WHERE a.Math>b.Math) AS ScoresBelow
,((SELECT COUNT(*) FROM ExamResults c WHERE a.Math=c.Math)*.5) AS ScoresEqualTimesPointFive
,(SELECT COUNT(*) FROM ExamResults) AS TotalScores
,((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 PercentileRank
FROM ExamResults a
ORDER BY Math DESC

Garth
www.SQLBook.com

udayfn12
Starting Member

15 Posts

Posted - 2002-07-31 : 10:00:43

quote:

This should get you started...

SELECT Math,
(SELECT COUNT(*) FROM ExamResults b WHERE a.Math>b.Math) AS ScoresBelow
,((SELECT COUNT(*) FROM ExamResults c WHERE a.Math=c.Math)*.5) AS ScoresEqualTimesPointFive
,(SELECT COUNT(*) FROM ExamResults) AS TotalScores
,((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 PercentileRank
FROM ExamResults a
ORDER BY Math DESC

Garth
www.SQLBook.com

udayfn12
Starting Member

15 Posts

Posted - 2002-07-31 : 10:01:19

Thanks a lot Garth.

-Reddy.

Edited by - udayfn12 on 07/31/2002 10:04:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources