sum salary over PARTITION BY : OVER « Analytical Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Analytical Functions
  3. OVER
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL>
SQL> break on mgr
SQL>
SQL> select mgr, ename, sal
  2  ,      sum(sal) over
  3         ( PARTITION BY mgr
  4           order by mgr, sal, empno
  5           range unbounded preceding
  6         ) as cumulative
  7  from   emp
  8  order  by mgr, sal;

       MGR ENAME           SAL CUMULATIVE
---------- -------- ---------- ----------
         4 Smart          3000       3000
           Fake           3000       6000
         6 Jane            800        800
           Wil            1250       2050
           Mary           1250       3300
           Take           1500       4800
           Jack           1600       6400
         7 Mike           1300       1300
         8 Ana            1100       1100
         9 Chris          2450       2450
           Black          2850       5300
           Jane           2975       8275
        13 Tom             800        800
           Peter          5000       5000

14 rows selected.

SQL>
SQL> --clear breaks
SQL>
SQL>
SQL> drop table emp;

Table dropped.








16.20.OVER
16.20.1.AVG(salary) OVER
16.20.2.ROUND(AVG(salary) OVER())
16.20.3.Use partitioning in the OVER clause of the aggregate-analytical function
16.20.4.Ratio-to-Report
16.20.5.Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
16.20.6.Using Analytic Functions: AVG(Mark) OVER
16.20.7.Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
16.20.8.Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
16.20.9.sum over (nothing)
16.20.10.sum over partition by, order by
16.20.11.sum salary over PARTITION BY