Oracle SQL - Use common aggregate functions as analytical functions

  1. HOME
  2. Oracle SQL
  3. Analytic Functions
  4. Analytic Functions

Introduction

You can use common aggregate functions as analytical functions: SUM, AVG, COUNT, STDDEV, VARIANCE, MAX, and MIN.

The following code two code display each person's employee number, name, original salary, and the average salary of all emp. 

SELECT e.empno, e.ename, e.orig_salary, 
  x.aos "Avg. salary" 
FROM emp e, 
  (SELECT AVG(orig_salary) aos FROM emp) x 
ORDER BY ename;

--It can be done easily using AVG in an analytical function: 

SELECT empno, ename, orig_salary, 
  AVG(orig_salary) OVER() "Avg. salary" 
FROM emp 
ORDER BY ename

Demo

SQL>
SQL>--   w  w  w. j  a  va 2 s  .com
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL> SELECT e.empno, e.ename, e.orig_salary,
  2    x.aos "Avg. salary"
  3  FROM emp e,
  4    (SELECT AVG(orig_salary) aos FROM emp) x
  5  ORDER BY ename;

     EMPNO ENAME      ORIG_SALARY Avg. salary
---------- ---------- ----------- -----------
         1 Alison           45000       39600
         3 Celia                        39600
         6 David            78000       39600
         2 James            23000       39600
         7 Jode             21000       39600
         5 Linda                        39600
         4 Robert           31000       39600

7 rows selected.

SQL>
SQL> --It can be done easily using AVG in an analytical function:
SQL>
SQL> SELECT empno, ename, orig_salary,
  2    AVG(orig_salary) OVER() "Avg. salary"
  3  FROM emp
  4  ORDER BY ename
  5

Related Topic