Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# MEDIAN function
<a name="r_MEDIAN"></a>

Calculates the median value for the range of values. `NULL` values in the range are ignored.

MEDIAN is an inverse distribution function that assumes a continuous distribution model.

MEDIAN is a special case of [PERCENTILE\$1CONT](r_PERCENTILE_CONT.md).

## Syntax
<a name="r_MEDIAN-synopsis"></a>

```
MEDIAN(median_expression)
```

## Arguments
<a name="r_MEDIAN-arguments"></a>

 *median\$1expression*   
The target column or expression that the function operates on.

## Data types
<a name="r_MEDIAN-data-types"></a>

The return type is determined by the data type of *median\$1expression*. The following table shows the return type for each *median\$1expression* data type.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html)

## Usage notes
<a name="r_MEDIAN-data-type-usage-notes"></a>

If the *median\$1expression* argument is a `DECIMAL` data type defined with the maximum precision of 38 digits, it is possible that MEDIAN will return either an inaccurate result or an error. If the return value of the MEDIAN function exceeds 38 digits, the result is truncated to fit, which causes a loss of precision. If, during interpolation, an intermediate result exceeds the maximum precision, a numeric overflow occurs and the function returns an error. To avoid these conditions, we recommend either using a data type with lower precision or casting the *median\$1expression* argument to a lower precision. 

If a statement includes multiple calls to sort-based aggregate functions (LISTAGG, PERCENTILE\$1CONT, or MEDIAN), they must all use the same ORDER BY values. Note that MEDIAN applies an implicit order by on the expression value. 

For example, the following statement returns an error. 

```
SELECT TOP 10 salesid, SUM(pricepaid), 
PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid),
MEDIAN(pricepaid)
FROM sales 
GROUP BY salesid, pricepaid;

An error occurred when executing the SQL command:
SELECT TOP 10 salesid, SUM(pricepaid), 
PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid),
MEDIAN(pricepaid)
FROM sales 
GROUP BY salesid, pricepaid;

ERROR: within group ORDER BY clauses for aggregate functions must be the same
```

The following statement runs successfully. 

```
SELECT TOP 10 salesid, SUM(pricepaid), 
PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid),
MEDIAN(salesid)
FROM sales 
GROUP BY salesid, pricepaid;
```

## Examples
<a name="r_MEDIAN-examples"></a>

The following examples use the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).

The following example shows that MEDIAN produces the same results as PERCENTILE\$1CONT(0.5). 

```
SELECT TOP 10 DISTINCT sellerid, qtysold, 
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold),
MEDIAN(qtysold) 
FROM sales
GROUP BY sellerid, qtysold;

+----------+---------+-----------------+--------+
| sellerid | qtysold | percentile_cont | median |
+----------+---------+-----------------+--------+
|        2 |       2 |               2 |      2 |
|       26 |       1 |               1 |      1 |
|       33 |       1 |               1 |      1 |
|       38 |       1 |               1 |      1 |
|       43 |       1 |               1 |      1 |
|       48 |       2 |               2 |      2 |
|       48 |       3 |               3 |      3 |
|       77 |       4 |               4 |      4 |
|       85 |       4 |               4 |      4 |
|       95 |       2 |               2 |      2 |
+----------+---------+-----------------+--------+
```

The following example finds the median quantity sold for each sellerid.

```
SELECT sellerid, 
MEDIAN(qtysold)
FROM sales
GROUP BY sellerid
ORDER BY sellerid
LIMIT 10;

+----------+--------+
| sellerid | median |
+----------+--------+
|        1 |    1.5 |
|        2 |      2 |
|        3 |      2 |
|        4 |      2 |
|        5 |      1 |
|        6 |      1 |
|        7 |    1.5 |
|        8 |      1 |
|        9 |      4 |
|       12 |      2 |
+----------+--------+
```

To verify the results of the previous query for the first sellerid, use the following example.

```
SELECT qtysold 
FROM sales 
WHERE sellerid=1;

+---------+
| qtysold |
+---------+
|       2 |
|       1 |
+---------+
```