Median Analytic Functions in Oracle SQL

Median Analytic Functions in Oracle SQL

MEDIAN
MEDIAN function is fetch the middle value from set of sorted data. The “median” is the “middle” value in the list of numbers.
NULL value is ignored in the calculation.

ODD n number of rows: Arrange number from smaller to higher and then Median is exactly the middle number.
EVEN n number of rows: Arrange number from smaller to higher and Median is calculated result value as sum of two middle numbers divide by 2.

Syntax: MEDIAN(expr) [ OVER (query_partition_clause) ]

Examples of Median function:

Fetch the median salary for each department in the hr.employees table

SELECT department_id, MEDIAN(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;

DEPARTMENT_ID MEDIAN(SALARY)
------------- --------------
           10           4400
           20           9500
           30           2850
           40           6500
           50           3100
           60           4800

Finding department wise median salary with employee detail

SELECT department_id,employee_id, salary,
MEDIAN(salary) OVER (PARTITION BY department_id) "Median by department"
FROM employees
-- WHERE department_id > 60
ORDER BY department_id, salary;

DEPARTMENT_ID EMPLOYEE_ID     SALARY Median by department
------------- ----------- ---------- --------------------
           10         200       4400                 4400
           20         202       6000                 9500
           20         201      13000                 9500
           30         119       2500                 2850
           30         118       2600                 2850
           30         117       2800                 2850
           30         116       2900                 2850
           30         115       3100                 2850
           30         114      11000                 2850
           40         203       6500                 6500

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.