Updated: 31 July 2015

Use the table-valued function PERCENTILES to calculate any number of percentiles from a set of values.

SELECT * FROM [wct].[PERCENTILES](

<@xQuery, nvarchar(max),>

,<@P, nvarchar(max),>

,<@Pmeth, nvarchar(4000),>)

A SQL statement which returns a resultant table consisting of a single column of numeric data sorted in ascending order. @xQuery

A comma separated list of values between 0 and 1 from which the percentile values are calculated.

The percentile calculation method. Use 'INC' to return the equivalent of the Excel PERCENTILE.INC function and 'EXC' for return the equivalent of the PERCENTILE.EXC function.

RETURNS TABLE (

[P] [float] NULL,

[V] [float] NULL

)

the percentile as supplied in *@P*.

the P^{th} percentile value

· Any values less than zero or greater than 1 in *@P* are ignored.

· If *@P* IS NULL, then PERCENTILES generates values from 0 to 1 in increments of .01.

· Percentile values are interpolated when the requested percentile lies between 2 values in the ordered resultant table.

· If the results of @xQuery are not in ascending order, then the percentiles returned by the function will be unreliable.

In this example we will select the 25^{th}, 50^{th}, 75^{th} and 100^{th} percentiles of the ordered list {3,6,7,8,8,10,13,15,16,20} using the 'INC' method.

SELECT

*

FROM

wct.PERCENTILES(

'SELECT x FROM (VALUES (3),(7),(8),(10),(13),(15),(8),(16),(20),(6))n(x) ORDER BY 1'

,'0.25,0.50,0.75,1'

,'INC'

)

This produces the following result.

In this example we use the SeriesFloat function to insert 100,000 rows from a normal distribution having a mean of 100 and a standard deviation of 15 and we return all the percentiles from 0 to 1 in increments of .01 using the 'INC' method.

SELECT

ROUND(SeriesValue,0) as X

INTO

#g

FROM

wct.SeriesFloat(100,15,NULL,100000,'N')

SELECT

*

FROM

wct.PERCENTILES('SELECT X FROM #g ORDER BY 1',NULL,NULL)

ORDER BY

1

DROP TABLE

#g

This produces the following result.

In this example we insert 10,000 rows from a normal distribution with a mean of 50 and a standard deviation of 25 into a table and calculate the percentiles from 0 to 1 in increments of .01 using the 'EXC' method.

SELECT

ROUND(SeriesValue,1) as X

INTO

#g

FROM

wct.SeriesFloat(50,25,NULL,10000,'N')

SELECT

*

FROM

wct.PERCENTILES('SELECT X FROM #g ORDER BY 1',NULL,'EXC')

ORDER BY

1

DROP TABLE

#g

This produces the following result.

In this example we calculate the 64

SELECT

*

FROM

wct.PERCENTILES(

'SELECT x FROM (VALUES (15),(20),(35),(40),(50))n(x) ORDER BY 1'

,0.64

,'EXC'

)

This produces the following result.