Updated: 6 August 2010
Use QUARTILE to return the quartile of a dataset.
Syntax
SELECT [wctStatistics].[wct].[QUARTILE] (
<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@Quart, float,>)
Arguments
@Values_TableName
the name, as text, of the table or view that contains the values to be used in the QUARTILE 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 QUARTILE 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.
@Quart
indicates which value to return. @Quart is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If @Quart is less than zero or if @Quart is greater than one, QUARTILE returns an error.
· MIN, MEDIAN, and MAX return the same value as QUARTILE when @Quart is equal to zero, two and 4, respectively.
· If @Quart = 0, then QUARTILE returns the MIN of the dataset.
· If @Quart = 4, then QUARTILE returns the MAX of the dataset.
· If @Quart between 1 and 3, then QUARTILE returns the PERCENTILE of the dataset where @K = @Quart/4.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #q1(
[num] [float] NOT NULL
)
INSERT INTO #q1 VALUES (1)
INSERT INTO #q1 VALUES (2)
INSERT INTO #q1 VALUES (4)
INSERT INTO #q1 VALUES (7)
INSERT INTO #q1 VALUES (8)
INSERT INTO #q1 VALUES (9)
INSERT INTO #q1 VALUES (10)
INSERT INTO #q1 VALUES (12)
To return the 0th quartile
select wct.QUARTILE('#q1','num','',NULL,0)
This produces the following result
----------------------
1
(1 row(s) affected)
To return the 1st quartile
select wct.QUARTILE('#q1','num','',NULL,1)
This produces the following result
----------------------
3.5
(1 row(s) affected)