MEDIAN aggregate function

The MEDIAN function returns the median value in a set of values.

The schema is SYSIBM.

expression: An expression that specifies the set of values from which the median is determined. The expression must return a value that is a built-in numeric data type, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported through implicit casting. If the expression is not a numeric data type, it is cast to DECFLOAT(34) before the function is evaluated.

If the data type of expression is DECFLOAT(n), the data type of the result is DECFLOAT(34). Otherwise, the data type of the result is DOUBLE.

The function is applied to the set of values that are derived from the argument values by the elimination of null values.

The result can be null. If expression is null or if the function is applied to an empty set, the result is a null value.

The MEDIAN function is a synonym for the following expression:

PERCENTILE_CONT( 0.5 ) WITHIN GROUP( ORDER BY expression )

Example

Set the host variable MED to the value that corresponds to the median of the salaries of the employees in department 'E21'.

   SELECT MEDIAN(SALARY) INTO :MED FROM EMPLOYEE WHERE WORKDEPT = 'E21'

MED is set to a value of 41895.00.

The following result set is shown for reference.

   SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'E21' ORDER BY SALARY

SALARY     
-----------
   31840.00
   35370.00
   39950.00
   43840.00
   45370.00
   86150.00

  6 record(s) selected.