[thelist] SQL Server 2008 - How to get the 95% percentile?

[Anthony Baratta]

Albeit I'm dredging up my stats knowledge from way back in the dusty corners of the brain....

but the idea is to remove 5% of the total "population" to get ride of "outliers". e.g. 

SELECT AVG(score) FROM dbo.TestScores
WHERE score IN (1,2,3,4,5,6,7,8,9,20)

the Average is 6. But if we limit it to the 95th Percentile, we drop 20, and average the rest.

SELECT AVG(score) FROM dbo.TestScores
WHERE score IN (1,2,3,4,5,6,7,8,9)

and get 5.

The quick and dirty is to remove the "highest" 5%. But obviously you could go the other way and remove the bottom 5%. What I don't remember is, can you go both ways by 2.5%?

And I added the "with ties" because you only want to remove the scores that are outside the 95%, but keep the ones that "tie" at the end.

e.g. what if your population was:

1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,50

You only want to drop the 50, not any of the 4's. That would still meet the 95th Percentile for the "score" but not for the population. And I think that is acceptable under "general statistical practices" (tm). :-)

Anyway - that's my two Euros. ;-)

--
Anthony Baratta