# Calculate with Analytical functions : Introduction « Analytical Functions « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
 1 Introduction 2 Query Select 3 Set 4 Insert Update Delete 5 Sequences 6 Table 7 Table Joins 8 View 9 Index 10 SQL Data Types 11 Character String Functions 12 Aggregate Functions 13 Date Timestamp Functions 14 Numerical Math Functions 15 Conversion Functions 16 Analytical Functions 17 Miscellaneous Functions 18 Regular Expressions Functions 19 Statistical Functions 20 Linear Regression Functions 21 PL SQL Data Types 22 PL SQL Statements 23 PL SQL Operators 24 PL SQL Programming 25 Cursor 26 Collections 27 Function Procedure Packages 28 Trigger 29 SQL PLUS Session Environment 30 System Tables Data Dictionary 31 System Packages 32 Object Oriented 33 XML 34 Large Objects 35 Transaction 36 User Privilege
 Oracle PL/SQL Tutorial » Analytical Functions » Introduction
16.1.6.Calculate with Analytical functions
 ```SQL> SQL> -- create demo table SQL> create table Employee(   2    ID                 VARCHAR2(4 BYTE)         NOT NULL,   3    First_Name         VARCHAR2(10 BYTE),   4    Last_Name          VARCHAR2(10 BYTE),   5    Start_Date         DATE,   6    End_Date           DATE,   7    Salary             Number(8,2),   8    City               VARCHAR2(10 BYTE),   9    Description        VARCHAR2(15 BYTE)  10  )  11  / Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)   2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')   3  / 1 row created. SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)   2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')   3  / 1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee   2  / ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer 02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester 03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester 04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager 05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester 06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester 07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager 08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester 8 rows selected. SQL> SQL> SQL> SELECT id, first_name, description, salary,   2    ROUND(AVG(salary) OVER(PARTITION BY description))   3        "Avg-group",   4    ROUND(salary - AVG(salary) OVER(PARTITION   5        BY description)) "Diff."   6  FROM employee   7  ORDER BY description, first_name   8  / ID   FIRST_NAME DESCRIPTION         SALARY  Avg-group      Diff. ---- ---------- --------------- ---------- ---------- ---------- 04   Celia      Manager            2344.78       5121      -2777 07   David      Manager            7897.78       5121       2777 01   Jason      Programmer         1234.56       1235          0 02   Alison     Tester             6661.78       4219       2442 03   James      Tester             6544.78       4219       2325 08   James      Tester             1232.78       4219      -2987 06   Linda      Tester             4322.78       4219        103 05   Robert     Tester             2334.78       4219      -1885 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee   2  / Table dropped. SQL> SQL>```
 16.1.Introduction 16.1.1. The Analytical Functions in Oracle (Analytical Functions I) 16.1.2. The Order in Which the Analytical Function Is Processed in the SQL Statement 16.1.3. Using the Analytic Functions 16.1.4. Using the Ranking Functions 16.1.5. More Than One Analytical Function May Be Used in a Single Statement 16.1.6. Calculate with Analytical functions