Oracle Aggregate Function - Oracle/PLSQL MEDIAN Function

This Oracle tutorial explains how to use the Oracle/PLSQL MEDIAN function.

The Oracle/PLSQL MEDIAN function returns the median of an expression.

MEDIAN returns the median value.

MEDIAN operates on a group of rows and return one row of output. Null values are ignored by MEDIAN. You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL MEDIAN function is:

MEDIAN( expression ) [ OVER ( query partition clause ) ]

expression is the value used to calculate the median.

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from  w ww.  j av  a2s  .co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT MEDIAN(sal)
  2  FROM emp;

MEDIAN(SAL)
-----------
       1600

SQL>

Using the MEDIAN function with the group by clause:


SQL> select MEDIAN(sal) from emp group by deptno;

MEDIAN(SAL)
-----------
       2925
     2237.5
       1500

SQL>