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  Site Related Forums  Article Discussion  Article: Computing Percentiles in SQL Server

Author  Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

 Posted - 2004-05-20 : 10:47:15 Say you score 570 on the verbal section of the SAT. How well did you do? According to the College Board, 570 falls into the 71st percentile. This means you did better than 71% of the other students who took the test. Percentiles are a useful way to present rankings among a large population. You may want to add percentiles to reports you produce with SQL. This article by Bob Newstadt shows how to calculate a percentile for a given group of scores. It also covers finding a score for a given percentile.Article Link.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

 Posted - 2004-05-21 : 04:38:08 Interesting article.....and an even more enterprising and innovative approach to advertising your services....but at least you are offering something in return.

Bill Wilkinson
Starting Member

7 Posts

 Posted - 2004-12-21 : 17:19:56 Ummm...aren't there some much easier ways to do some of these things, using TSQL?Example: "What is the score that represnts the 71st percentile?"SELECT MAX(score) FROM ( SELECT TOP 71 PERCENT score FROM scores ORDER BY score )Yes, I see that doesn't cover, by any means, what the article covers. But for some questions...Credit to Xander Sherry for rubbing my nose in this one.

dalepres
Starting Member

1 Post

 Posted - 2005-10-12 : 09:28:45 Mr. Wilkerson's reply returns the maximum value from the data that is within the percentile but does not give the actual value that would, if it existed, match the specified percentile. In other words, if you used TOP 15 PERCENT, his method would return the maximum data value less than or equal to the 15th percentile but not the actual value of the 15th percentile. If you are trying to match Excel's PERCENTILE function, you have to use the interpolation to get the actual value.

sqlghost
Starting Member

23 Posts

 Posted - 2006-10-31 : 07:51:50 This percentile function takes a looooong time.Using 10,000 rows of data it took 2 minutes and 10 seconds on my computer.This is how to get the 10,000 rows into the table:`Declare @TestScores table (StudentID int identity(1,1), Score int)insert @TestScores (Score) Values (20)insert @TestScores (Score) Values (03)insert @TestScores (Score) Values (40)insert @TestScores (Score) Values (45)insert @TestScores (Score) Values (50)insert @TestScores (Score) Values (20)insert @TestScores (Score) Values (90)insert @TestScores (Score) Values (20)insert @TestScores (Score) Values (11)insert @TestScores (Score) Values (30)while @@ROWCOUNT > 0insert @TestScores select t.Score from @TestScores tcross join(select max (StudentID) MaxRowNum from @TestScores) xwheret.StudentID <= 10000 - x.MaxRowNum`That code puts the same scores in 1000 times, so you should get the same results as in the original article.Here is a modification that is a bit faster (takes less than 1 second):`declare @pp floatset @pp = .5declare @k int, @d float, @ax float, @bx floatdeclare @values table (i int identity(1,1), x float)insert @values select Score from @TestScores order by Scoreselect @k=floor(kf), @d=kf-floor(kf)from (select 1+@pp*(count(*)-1) as kf from @values) as x1select @ax=x from @values where i=@kselect @bx=x from @values where i=@k+1 select @pp as factor, dbo.LERP(@d, 0.0, 1.0, @ax, @bx) as percentile`Personally I think LINTERP would be a nicer name than LERP.

ChrisBui2008

3 Posts

 Posted - 2008-12-05 : 16:41:14 Great Job SQLGhost! The modification to the query made it extremly fast!Chris Bui

## Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Articles

Forums

Blogs

Contact Us

About the Site