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

@Quart

indicates which value to return. @Quart is an expression of type float or of a type that can be implicitly converted to float.

@Values_RangeQuery

the select statement, as text, used to determine the first set of values to be used in the QUARTILE_q calculation.

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);