Databases Reference
In-Depth Information
In this example, you can see that the
partition
keyword is omitted; therefore there's
only one single group. There is, however, a window that is expanding with each row.
The window is determined by the
order by
clause in line 4. The (implicit) window
clause in this example is
RANGE UNBOUNDED PRECEDING
, which is the default for the
Window
clause. This means that all preceding salaries are added to the current row's
salary, thereby creating a running total.
If we look at
EMPNO 7521
(
WARD
), we can see that the
OVERALL_TOTAL
column
shows
3650
. This is a summary of all preceding salaries, including Ward's
(800 + 1600 + 1250). To make this a running total per department, simply add a
partition
clause as done in the following example:
SQL> select empno
2 , ename
3 , sal
4 , deptno
5 , sum (sal) over (partition by deptno
6 order by empno
7 ) department_total
8 from emp
9 order by deptno, empno
10 /
EMPNO ENAME SAL DEPTNO DEPARTMENT_TOTAL
---------- ---------- ---------- ---------- ----------------
7782 CLARK 2450 10 2450
7839 KING 5000 10 7450
7934 MILLER 1300 10 8750
7369 SMITH 800 20 800
7566 JONES 2975 20 3775
7788 SCOTT 3000 20 6775
7876 ADAMS 1100 20 7875
7902 FORD 3000 20 10875
7499 ALLEN 1600 30 1600
7521 WARD 1250 30 2850
7654 MARTIN 1250 30 4100
7698 BLAKE 2850 30 6950
7844 TURNER 1500 30 8450
7900 JAMES 950 30 9400
