Sum over partition by and order by : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Analytical Functions » Aggregrate Analytical 
Sum over partition by and order by
 
SQL>
SQL>
SQL> set echo on
SQL> break on deptno skip 1
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(72),
  7                    COMM NUMBER(72),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES (7499'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 160030030);

row created.

SQL> INSERT INTO EMP VALUES (7521'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 125050030);

row created.

SQL> INSERT INTO EMP VALUES (7566'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES (7654'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250140030);

row created.

SQL> INSERT INTO EMP VALUES (7698'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES (7782'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES (7788'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES (7839'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES (7844'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500030);

row created.

SQL> INSERT INTO EMP VALUES (7876'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES (7900'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES (7902'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES (7934'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

row created.

SQL>
SQL>
SQL> select deptno, ename, sal,
  2    sum(salover
  3      (partition by deptno
  4       order by ename
  5       rows precedingsliding_total
  6  from emp
  7  order by deptno, ename
  8  /

DEPTNO ENAME            SAL SLIDING_TOTAL
------ ---------- --------- -------------
    10 CLARK        2450.00       2450.00
       KING         5000.00       7450.00
       MILLER       1300.00       8750.00

    20 ADAMS        1100.00       1100.00
       FORD         3000.00       4100.00
       JONES        2975.00       7075.00
       SCOTT        3000.00       8975.00
       SMITH         800.00       6775.00

    30 ALLEN        1600.00       1600.00

DEPTNO ENAME            SAL SLIDING_TOTAL
------ ---------- --------- -------------
    30 BLAKE        2850.00       4450.00
       JAMES         950.00       5400.00
       MARTIN       1250.00       5050.00
       TURNER       1500.00       3700.00
       WARD         1250.00       4000.00


14 rows selected.

SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>

 
Related examples in the same category
1.count(*) over partition, order by, range unbounded preceding
2.count(*) over partition by, order by, range unbounded preceding
3.Employee salary report with avg salary for the previous 12 months
4.avg over range between
5.Is our average total_order_price increasing or decreasing?
6.analytic order-by clause
7.avg over and avg over order by
8.Sum over order by
9.avg over order by range
10.average 5 before, after
11.Row-ordering is done first and then the moving average
12.Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
13.SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
14.Use the COUNT aggregate analytical function to show how many rows are included in each window
15.To see how the moving average window can expand
16.Uses dates and logical offset of seven days preceding
17.A seven-day MAX and MIN on Tuesdays
18.A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
19.Displaying a Running Total Using SUM as an Analytical Function
20.Reporting on a Sum
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.