Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server QUARTILE function


 
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)


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service