Updated: 6 August 2010
Use PERCENTILE to calculate the kth percentile of value in a dataset. You can use this function to establish criteria for acceptance or rejection.
Syntax
SELECT [wctStatistics].[wct].[PERCENTILE] (
<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@K, float,>)
Arguments
@Values_TableName
the name, as text, of the table or view that contains the values to be used in the PERCENTILE calculation.
@ColumnName
the name, as text, of the column in the table or view specified by @Values_TableName that contains the values to be used in the percentile calculation.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@K
is the percentile value in the range 0 through 1, inclusive. @K is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If @K < 0 or @K > 1 , PERCENTILE returns an error.
· If @K is not a multiple of 1/(n-1), PERCENTILE interpolates to determine the value at the kth percentile.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #p1(
[num] [float] NOT NULL
)
INSERT INTO #p1 VALUES (1000)
INSERT INTO #p1 VALUES (8000)
INSERT INTO #p1 VALUES (8000)
INSERT INTO #p1 VALUES (13000)
To select the first percentile:
select wct.PERCENTILE('#p1','num','',NULL,.01)
This produces the following result
----------------------
1210
(1 row(s) affected)
To select the 25th percentile:
select wct.PERCENTILE('#p1','num','',NULL,.25)
This produces the following result
----------------------
6250
(1 row(s) affected)
To select the 75th percentile:
select wct.PERCENTILE('#p1','num','',NULL,.75)
This produces the following result
----------------------
9250
(1 row(s) affected)