Getting the 90th percentile of a list of values

Hi,

I need to calculate the 90th percentile of a list of values: 

0.0099

0.0129

0.0031

0.0219

0.2632

0.0124

0.0493

0.05

0.0433

how would I go about the calculation? I know the answer I need to come up with is 0.0713,9. Any suggestions?

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05) 
INSERT INTO @Temp VALUES(0.0433)

SELECT DATA
FROM @Temp
ORDER BY DATA ASC

--90th percentile 
SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 90 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 10 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2.0

May 20th, 2015 1:27am

I get different value

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05) 
INSERT INTO @Temp VALUES(0.0433)

SELECT *,0.90* COUNT(*) OVER () FROM @Temp
ORDER BY DATA

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 1:47am

My query currently returns 0,2632

where I need to get 0.0713,9 what am I doing wrong. 

May 20th, 2015 1:50am

Hi,

90th Percentile

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 3:07am

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05) 
INSERT INTO @Temp VALUES(0.0433)

select max(case when rownum*1.0/numrows <= 0.9 then DATA end) as percentile_90th
from (select DATA,
             row_number() over (order by DATA) as rownum,
             count(*) over () as numrows
      from @Temp
      where DATA is not null
     ) t
May 20th, 2015 3:11am

SQL Server 2012

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05) 
INSERT INTO @Temp VALUES(0.0433)

SELECT *,PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DATA)  OVER () AS MedianDisc 
FROM @Temp

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 3:20am



SELECT *, (COUNT(*) OVER () -1) *.9+1
FROM @Temp 
WHERE DATA BETWEEN FLOOR (DATA) AND CEILING(DATA)
May 20th, 2015 3:38am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics