MEDIAN function
Calculates the median value for the range of values. NULL
values in the range are
ignored.
MEDIAN is an inverse distribution function that assumes a continuous distribution model.
MEDIAN is a special case of PERCENTILE_CONT.
Syntax
MEDIAN(median_expression)
Arguments
 median_expression

The target column or expression that the function operates on.
Data types
The return type is determined by the data type of median_expression. The following table shows the return type for each median_expression data type.
Input type  Return type 

INT2 , INT4 , INT8 , NUMERIC , DECIMAL 
DECIMAL 
FLOAT , DOUBLE 
DOUBLE 
DATE 
DATE 
TIMESTAMP 
TIMESTAMP 
TIMESTAMPTZ 
TIMESTAMPTZ 
Usage notes
If the median_expression argument is a DECIMAL
data type
defined with the maximum precision of 38 digits, it is possible that MEDIAN will
return either an inaccurate result or an error. If the return value of the MEDIAN
function exceeds 38 digits, the result is truncated to fit, which causes a loss of
precision. If, during interpolation, an intermediate result exceeds the maximum
precision, a numeric overflow occurs and the function returns an error. To avoid
these conditions, we recommend either using a data type with lower precision or
casting the median_expression argument to a lower precision.
If a statement includes multiple calls to sortbased aggregate functions (LISTAGG, PERCENTILE_CONT, or MEDIAN), they must all use the same ORDER BY values. Note that MEDIAN applies an implicit order by on the expression value.
For example, the following statement returns an error.
SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid;
An error occurred when executing the SQL command: SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid; ERROR: within group ORDER BY clauses for aggregate functions must be the same
The following statement runs successfully.
SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(salesid) FROM sales GROUP BY salesid, pricepaid;
Examples
The following examples use the TICKIT sample database. For more information, see Sample database.
The following example shows that MEDIAN produces the same results as PERCENTILE_CONT(0.5).
SELECT TOP 10 DISTINCT sellerid, qtysold, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold), MEDIAN(qtysold) FROM sales GROUP BY sellerid, qtysold;
+++++  sellerid  qtysold  percentile_cont  median  +++++  2  2  2  2   26  1  1  1   33  1  1  1   38  1  1  1   43  1  1  1   48  2  2  2   48  3  3  3   77  4  4  4   85  4  4  4   95  2  2  2  +++++
The following example finds the median quantity sold for each sellerid.
SELECT sellerid, MEDIAN(qtysold) FROM sales GROUP BY sellerid ORDER BY sellerid LIMIT 10;
+++  sellerid  median  +++  1  1.5   2  2   3  2   4  2   5  1   6  1   7  1.5   8  1   9  4   12  2  +++
To verify the results of the previous query for the first sellerid, use the following example.
SELECT qtysold FROM sales WHERE sellerid=1;
++  qtysold  ++  2   1  ++