In actual application, sum () can be used to calculate the total or accumulated values in the group. The specific example is as follows:
1. Create a demo table
Create Table EMP
As
Select * from Scott. EMP;
ALTER TABLE EMP
Add constraint emp_pk
Primary Key (empno );
Create Table Dept
As
Select * from Scott. Dept;
Alter table Dept
Add constraint dept_pk
Primary Key (deptno );
2. The sum () statement is as follows:
Select deptno,
Ename,
Sal,
-- Accumulate by Department salary (order by changes the role of the analysis function and only works in the current row and the previous row, not all rows)
Sum (SAL) over (partition by deptno order by SAL) cumdepttot,
Sum (SAL) over (partition by deptno) salbydept, -- calculate the salary of a department
Sum (SAL) over (order by deptno, Sal) cumtot, -- the sum of the salaries of all employees in one row
Sum (SAL) over () totsal -- total salary statistics
From EMP
Order by deptno, Sal
3. The result is as follows:
10 Miller 1300.00 1300 8750 1300 29025
10 Clark 2450.00 3750 8750 3750 29025
10 King 5000.00 8750 8750 8750
20 Smith 800.00 800 10875 9550 29025
20 Adams 1100.00 1900 10875 10650
20 Jones 2975.00 4875 10875 13625 29025
20 Scott 3000.00 10875 10875 19625
20 Ford 3000.00 10875 10875 19625 29025
30 James 950.00 950 9400 20575
30 ward 1250.00 3450 9400 23075
30 Martin 1250.00 3450 9400 23075 29025
30 Turner 1500.00 4950 9400 24575 29025
30 Allen 1600.00 6550 9400 26175
30 Blake 2850.00 9400 9400 29025 29025
