MEDIAN( [ ALL | DISTINCT ] numeric-expression  )

MEDIAN( [ ALL ]  numeric-expression ) OVER ( window-spec )

window-spec : see Syntax 2 instructions in the Remarks section below

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. However, a NULL value is returned for a group that contains no rows.

numeric-expression values can be of any numeric data type other than BIT. See Numeric data types.

The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, the median is not unique so MEDIAN returns the mean of the two middle values. At most, half the population have values less than the median, and half have values greater than the median. If both groups contain less than half the population, then some of the population is exactly equal to the median. For example, if a < b < c, then the median of the list {a, b, c} is b. If a < b < c < d, then the median of the list {a, b, c, d} is the mean of b and c ( (b + c)/2).

If the result of the mean of the two middle elements has digits after the decimal place, they are truncated if the input data type can not represent them. To avoid this truncation, cast the input to a numeric type that allows digits after the decimal place.

Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition provided in WINDOW clause.

window-spec can only be over a partition (it cannot contain a ROW or RANGE specification). DISTINCT is not supported if a WINDOW clause is used. CUBE, ROLLUP, and GROUPING SETS are supported with syntax 1.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.

• SUM function [Aggregate]
• COUNT function [Aggregate]

The following statement returns the median salary from the Employees table.

SELECT MEDIAN( Salary ) FROM Employees;

The following statement returns the median salary by state from the Employees table:

SELECT EmployeeID, Surname, Salary, State,
  MEDIAN( Salary ) OVER Salary_Window
FROM Employees
WINDOW Salary_Window AS ( PARTITION BY State )
ORDER BY State, Surname;