8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- AVG and MEDIAN as Aggregate Functions
- AVG (Mean) Analytic Function
- MEDIAN Analytic Function
- Quick Links
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.
For more information see:
Hope this helps. Regards Tim...