QUARTILE_q

Updated 2023-10-24 13:38:35.567000

Syntax

SELECT [westclintech].[wct].[QUARTILE_q] (
   <@Values_RangeQuery, nvarchar(4000),>
  ,<@Quart, float,>)

Description

Use QUARTILE_q to return a quartile of a dataset.

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 Type

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

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"1"}]}

To return the 1st quartile

select wct.QUARTILE_q('SELECT num from #q1', 1);

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"3.5"}]}