MEDIAN
The MEDIAN function returns the median of a set of numbers.
- numeric-expression
- An expression that returns a built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.
If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is a double precision floating-point number.
The function is applied to the set of values derived from the argument values by the elimination of null values.
The result can be null. If numeric-expression is null or the function is applied to the empty set, the result is the null value.
Notes®
When used in an OLAP specification, only the window-partition-clause can be specified.
Examples
- Calculate the median salary of the employees in department D11
from the EMPLOYEE table.
The result is 24680.00. Department D11 has 11 employees. The middle row of a group of 11 values is the sixth row. The result of MEDIAN over that group is the value of the sixth row, which is 24680.00.SELECT MEDIAN(SALARY) FROM EMPLOYEE WHERE WORKDEPT = ‘D11’;
- Calculate the median salary of the employees in department E21
from the EMPLOYEE table.
The result is 24605.00. Department E21 has six employees. Because there are an even number of rows, the MEDIAN is computed by interpolating a value between the middle two rows. The middle two rows are the third row with the value 23840.00 and the fourth row with the value 25370.00. MEDIAN is computed by averaging those two values, which is 24605.00.SELECT MEDIAN(SALARY) FROM EMPLOYEE WHERE WORKDEPT = ‘E21’;