Returns the median of an expression.
Parameter |
Description |
---|---|
expression |
A numeric expression for which a median value is to be computed. |
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.
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.
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.
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:
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 |