MEDIAN Function [Aggregate]

Returns the median of an expression.

Syntax 1

MEDIAN([ALL | DISTINCT] expression)

Syntax 2

MEDIAN([ALL | DISTINCT] expression)

OVER (window-spec)

Parameters

Parameter	Description
expression	A numeric expression for which a median value is to be computed.

Usage

The median is the number separating the higher half of a sample, a population, or a probability distribution, from the lower half.

The data type of the returned value is the same as that of the input value. NULLs are ignored in the calculation of the median value. You can use the optional keyword DISTINCT to eliminate duplicate values before the aggregate function is applied. ALL, which performs the operation on all rows, is the default.

Note: ROLLUP and CUBE are not supported in the GROUP BY clause with Syntax 1.

Syntax 2 represents usage as a window function in a SELECT statement. As such, you can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement.

Note: The window-spec cannot contain a ROW, RANGE or ORDER BY specification; window-spec can only specify a PARTITION clause. DISTINCT is not supported if a WINDOW clause is used.

Example

The following query returns the median salary for each department in Florida:

SELECT DepartmentID, Surname, Salary,
MEDIAN(Salary) OVER (PARTITION BY DepartmentID) "Median"
FROM Employees
WHERE State IN ('FL')

The returned result is:

MEDIAN result set
DepartmentID	Surname	Salary	Median
100	Lull	87,900.000	73,870.000
100	Gowda	59,840.000	73,870.000
200	Sterling	64,900.000	76,200.000
200	Kelly	87,500.000	76,200.000
300	Litton	58,930.000	58,930.000
400	Francis	53,870.000	38,70.000
400	Charlton	28,300.000	53,870.000
400	Evans	68,940.000	53,870.000

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.