![MAX function [Aggregate]](arrow-left.gif){kind=icon}
![MIN function [Aggregate]](arrow-right.gif){kind=icon}
Returns the median of an expression.
MEDIAN([ALL | DISTINCT] expression)
MEDIAN([ALL | DISTINCT] expression)
OVER (window-spec)
window-spec: See Syntax 2 instructions in the Usage section, below.
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. For information on how to specify the window, see “Analytical functions”.
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 |
87900.000 |
73870.000 |
100 |
Gowda |
59840.000 |
73870.000 |
200 |
Sterling |
64900.000 |
76200.000 |
200 |
Kelly |
87500.000 |
76200.000 |
300 |
Litton |
58930.000 |
58930.000 |
400 |
Francis |
53870.000 |
3870.000 |
400 |
Charlton |
28300.000 |
53870.000 |
400 |
Evans |
68940.000 |
53870.000 |
