Updated: 6 August 2010
Use QUARTILE_q to return the quartile of a dataset.
Syntax
SELECT [wctStatistics].[wct].[QUARTILE_q] (
<@Values_RangeQuery, nvarchar(4000),>
,<@Quart, float,>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the first set of values to be used in the QUARTILE_q calculation.
@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_q returns an error.
· MIN, MEDIAN, and MAX return the same value as QUARTILE_q when @Quart is equal to zero, two and 4, respectively.
· If @Quart = 0, then QUARTILE_q returns the MIN of the dataset.
· If @Quart = 4, then QUARTILE_q returns the MAX of the dataset.
· If @Quart between 1 and 3, then QUARTILE_q 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_q('SELECT num from #q1',0)
This produces the following result
----------------------
1
(1 row(s) affected)
To return the 1st quartile
select wct.QUARTILE_q('SELECT num from #q1',1)
This produces the following result
----------------------
3.5
(1 row(s) affected)