8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

AVG (Mean) and MEDIAN Analytic Functions

This article gives an overview of the AVG (mean) and MEDIAN analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Setup

The examples in this article require the following table.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

AVG and MEDIAN as Aggregate Functions

The AVG and MEDIAN aggregate functions are used to calculate the mean and median values of a set of data respectively. As aggregate functions they reduce the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.

SELECT AVG(sal) AS mean_sal,
       MEDIAN(sal) AS media_sal
FROM   emp;

  MEAN_SAL  MEDIA_SAL
---------- ----------
2073.21429       1550

SQL>

We can get more granularity of information by including a GROUP BY clause. In the following example we see the mean and median values on a per-department basis.

SELECT deptno,
       AVG(sal) AS mean_sal,
       MEDIAN(sal) AS media_sal
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO   MEAN_SAL  MEDIA_SAL
---------- ---------- ----------
        10 2916.66667       2450
        20       2175       2975
        30 1566.66667       1375

SQL>

In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.

AVG (Mean) Analytic Function

The basic description for the AVG analytic function is shown below. The analytic clause is described in more detail here.

AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]

Using an empty OVER clause turns the AVG function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the mean salary for all employees, as well as all the original data.

SELECT empno,
       ename,
       deptno,
       sal,
       AVG(sal) OVER () AS mean_sal
FROM   emp
ORDER BY deptno;

     EMPNO ENAME          DEPTNO        SAL   MEAN_SAL
---------- ---------- ---------- ---------- ----------
      7782 CLARK              10       2450 2073.21429
      7839 KING               10       5000 2073.21429
      7934 MILLER             10       1300 2073.21429
      7566 JONES              20       2975 2073.21429
      7902 FORD               20       3000 2073.21429
      7876 ADAMS              20       1100 2073.21429
      7369 SMITH              20        800 2073.21429
      7788 SCOTT              20       3000 2073.21429
      7521 WARD               30       1250 2073.21429
      7844 TURNER             30       1500 2073.21429
      7499 ALLEN              30       1600 2073.21429
      7900 JAMES              30        950 2073.21429
      7698 BLAKE              30       2850 2073.21429
      7654 MARTIN             30       1250 2073.21429

SQL>

Adding the partitioning clause allows us to display the average salary per department, along with the employee data for each department.

SELECT empno,
       ename,
       deptno,
       sal,
       AVG(sal) OVER (PARTITION BY deptno) AS mean_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MEAN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ----------------
      7782 CLARK              10       2450       2916.66667
      7839 KING               10       5000       2916.66667
      7934 MILLER             10       1300       2916.66667
      7566 JONES              20       2975             2175
      7902 FORD               20       3000             2175
      7876 ADAMS              20       1100             2175
      7369 SMITH              20        800             2175
      7788 SCOTT              20       3000             2175
      7521 WARD               30       1250       1566.66667
      7844 TURNER             30       1500       1566.66667
      7499 ALLEN              30       1600       1566.66667
      7900 JAMES              30        950       1566.66667
      7698 BLAKE              30       2850       1566.66667
      7654 MARTIN             30       1250       1566.66667

SQL>

Median Analytic Function

The basic description for the MEDIAN analytic function is shown below. The analytic clause is described in more detail here.

MEDIAN(expr) [ OVER (query_partition_clause) ]

Using an empty OVER clause turns the MEDIAN function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the median salary for all employees, as well as all the original data.

SELECT empno,
       ename,
       deptno,
       sal,
       MEDIAN(sal) OVER () AS median_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MEDIAN_SAL
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800       1550
      7900 JAMES              30        950       1550
      7876 ADAMS              20       1100       1550
      7521 WARD               30       1250       1550
      7654 MARTIN             30       1250       1550
      7934 MILLER             10       1300       1550
      7844 TURNER             30       1500       1550
      7499 ALLEN              30       1600       1550
      7782 CLARK              10       2450       1550
      7698 BLAKE              30       2850       1550
      7566 JONES              20       2975       1550
      7788 SCOTT              20       3000       1550
      7902 FORD               20       3000       1550
      7839 KING               10       5000       1550

SQL>

Adding the partitioning clause allows us to display the median salary per department, along with the employee data for each department.

SELECT empno,
       ename,
       deptno,
       sal,
       MEDIAN(sal) OVER (PARTITION BY deptno) AS median_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MEDIAN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ------------------
      7934 MILLER             10       1300               2450
      7782 CLARK              10       2450               2450
      7839 KING               10       5000               2450
      7369 SMITH              20        800               2975
      7876 ADAMS              20       1100               2975
      7566 JONES              20       2975               2975
      7788 SCOTT              20       3000               2975
      7902 FORD               20       3000               2975
      7900 JAMES              30        950               1375
      7654 MARTIN             30       1250               1375
      7521 WARD               30       1250               1375
      7844 TURNER             30       1500               1375
      7499 ALLEN              30       1600               1375
      7698 BLAKE              30       2850               1375

SQL>

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.