Click or drag to resize
sqx

QUARTILE Structure

Aggregate [float] QUARTILE([float] Value, [smallint] Quartile, [smallint] Method)

Namespace:  sqx
Assembly:  SQX (in SQX.dll) Version: 1.0.2.6 (1.0.2.6)
Syntax
C#
Copy
[SerializableAttribute]
public struct QUARTILE : IBinarySerialize
Remarks
Quartile aggregate function.
Quartile Method:
  • 1 - This rule is also applied by QUARTIL.EXC function in Excel after Office 2010.
  • 2 - (Tukey's hinges). This rule is applied by Excel before Office 2010 and by the QUARTIL.INC function after Office 2010.
  • 3 - For odd N arithmetic mean of Methods 1 and 2, else Method 2.

Quartile: Returns the Quartile [1,2,3], 2 = Median.

NOTE: All parameters except Value must remain constant within the GROUP BY.

Attributes

Format.UserDefined, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, MaxByteSize = -1
Examples
SQL
Copy
WITH sample_data AS (SELECT X FROM (VALUES (6), (7), (15), (36), (39), (40), (41), (42), (43), (47), (49)) AS T(X))
--WITH sample_data AS (SELECT X FROM (VALUES (6), (7), (15), (36), (39), (40), (41), (42), (43), (47), (49), (50), (55)) AS T(X))
--WITH sample_data AS (SELECT X FROM (VALUES (6), (7), (15), (36), (39), (40), (41), (42), (43), (47), (49), (50)) AS T(X))
--WITH sample_data AS (SELECT X FROM (VALUES (7), (15), (36), (39), (40), (41)) AS T(X))
SELECT sqx.QUARTILE(X,1,1) Q1M1, sqx.QUARTILE(X,1,2) Q1M2
, sqx.QUARTILE(X,1,3) Q1M3, sqx.MEDIAN(X) Median
, sqx.QUARTILE(X,3,1) Q3M1, sqx.QUARTILE(X,3,2) Q3M2
, sqx.QUARTILE(X,3,3) Q3M3, COUNT(X) N
FROM sample_data
/*
Q1M1    Q1M2    Q1M3    Median  Q3M1    Q3M2    Q3M3    N
------- ------- ------- ------- ------- ------- ------- ------
15      25.5    20.25   40      43      42.5    42.75   11*/
See Also