# MEDIAN

## Syntax

```sql
MEDIAN
(
  expression  IN { smallint, int, bigint,
                    real, double precision,
                    timestamp, timestamptz, time, timetz, date }
)
RETURNS median;
```

## Overview

The MEDIAN aggregate function is a statistical function that calculates and returns the median of the values in a group. It is inspired by Oracle's MEDIAN function and implemented in PostgreSQL. 

It is available for integer, real, and date/time data types. Internally, the MEDIAN function works in two steps.

#### Aggregation step

* This step collects the values in the group into a type-correct vector (list data structure).
* NULL values are ignored in the aggregation.

#### Final calculation step

•    When all the values in the group have been collected, the median value is finally calculated.

•    First, we sort the values in the aggregated vector with a quick sort algorithm.

•    Returns the median value if the number of aggregated values is odd.

•    If the number of aggregated values is even, compute and return the average of the two values in the center.\
\
For date and time-related data with time zones, calculate the average value after timezoning the two center values to UTC.

Calculate the average value in UTC and adopt the timezone of the larger of the two center values.

#### Parameters

| Parameter  | Description                                                                                                                                                                     |
| ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| expression | <p>The value to be aggregated, which supports the</p><p>following data types. </p><ul><li>Integer types: <code>smallint</code>, <code>int</code>, <code>bigint</code></li></ul> |

* Real number type: `real(float4)`, `double precision`
* Date/Time types: `timestamp`, `timestamptz`, `time`, `timetz`, `date`  NULL values are ignored when aggregating, and values are stored in internal vectors for each type.

#### Caution

* If an even number of values are aggregated, the average of the two values in the center is calculated and returned.
* Internally, the C code uses the `qsort` function to sort the values.
* For date and time related data, additional timezone and type conversion processing is performed according to Oracle's processing logic.

## Example

```sql
# Test table
create table employees (name varchar, salary integer, hire_date timestamptz);
INSERT INTO employees (name, salary, hire_date) VALUES
('John Doe', 55000, '2020-03-15 09:00:00'),
('Jane Smith', 62000, '2019-07-22 10:30:00'),
('Michael Johnson', 72000, '2018-11-10 08:45:00'),
('Emily Davis', 48000, '2021-05-01 12:00:00'),
('David Wilson', 53000, '2017-09-17 14:20:00'),
('Sarah Brown', 60000, '2016-12-05 09:15:00'),
('James Taylor', 75000, '2015-06-30 16:45:00'),
('Jessica Martinez', 68000, '2022-01-25 11:10:00'),
('Daniel Anderson', 58000, '2020-10-05 13:35:00'),
('Laura Thomas', 49500, '2023-08-12 08:00:00');


-- Example of calculating the median for integer data
SELECT oracle.MEDIAN(salary) FROM employees;

 median 
--------
  59000
(1 row)


-- Example of calculating the median for datatype data
SELECT oracle.MEDIAN(hire_date) FROM employees;

         median         
------------------------
 2019-11-17 21:45:00+09
```

###


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tibero.com/tmaxopensql.en/tmax-o2-extensions/reference-guides/sql-function/aggregate-functions/median.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.