Categories:

Aggregate Functions (General) , Window Functions

# MEDIAN¶

Determines the median of a set of values.

## Syntax¶

Aggregate function

```MEDIAN( <expr> )
```

Window function

```MEDIAN( <expr> ) OVER ( [ PARTITION BY <expr2> ] )
```

## Argument¶

`expr`

The expression must evaluate to a numeric data type (`INTEGER`, `FLOAT`, `DECIMAL`, or equivalent).

## Returns¶

Returns a `FLOAT` or `DECIMAL` (fixed-point) number, depending upon the input.

## Usage Notes¶

• If the number of non-NULL values is an odd number greater than or equal to 1, this returns the median (“center”) value of the non-NULL values.

• If the number of non-NULL values is an even number, this returns a value equal to the average of the two center values. For example, if the values are 1, 3, 5, and 20, then this returns 4 (the average of 3 and 5).

• If all values are NULL, this returns NULL.

• If the number of non-NULL values is 0, this returns NULL.

• DISTINCT is not supported for this function.

• When used as a window function:

• This function does not support:

• ORDER BY sub-clause in the OVER() clause.

• Window frames.

## Examples¶

This shows how to use the function.

Create an empty table.

```CREATE OR REPLACE TABLE aggr(k int, v decimal(10,2));
```

Get the `MEDIAN` value for column v. The function returns NULL because there are no rows.

```SELECT MEDIAN (v) FROM aggr;
+------------+
| MEDIAN (V) |
|------------|
|       NULL |
+------------+
```

Insert some rows:

```INSERT INTO aggr VALUES(1, 10), (1,20), (1, 21);
INSERT INTO aggr VALUES(2, 10), (2, 20), (2, 25), (2, 30);
INSERT INTO aggr VALUES(3, NULL);
```

Get the `MEDIAN` value for each group. Note that because the number of values in group k = 2 is an even number, the returned value for that group is the mid-point between the two middle numbers.

```SELECT k, MEDIAN(v) FROM aggr GROUP BY k ORDER BY k;
+---+-----------+
| K | MEDIAN(V) |
|---+-----------|
| 1 |  20.00000 |
| 2 |  22.50000 |
| 3 |      NULL |
+---+-----------+
``` 