Teradata SUM Window Function

Sum Window Group Function:

SUM window function:  This SUM window group function permits an aggregate to be computed across the defined group. Groups are defined using the PARTITION BY clause. If PARTITION BY is not used then all rows are considered as a one group.   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY,SUM(SALARY) OVER ( ORDER        BY DEPARTMENTNO) FROM        EMPLOYEE2;   Employeeid DepartmentNo Salary Group Sum(Salary) 1 100 1000 102690.2 5 100 5000 102690.2 9 100 9000 102690.2 6 200 6000 102690.2 10 200 10000 102690.2 2 200 2000 102690.2 124 200 12345.11 102690.2 11 300 11000 102690.2 7 300 7000 102690.2 3 300 3000 102690.2 12 400 12000 102690.2 8 400 8000 102690.2 4 400 4000 102690.2 144 400 12345.11 102690.2 As PARTITION is not specified all rows will be considered as a part of same group. Note that column title is GROUP SUM indicates the group function. We can also write the same query as follows: SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER ( ORDER        BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND        UNBOUNDED FOLLOWING) FROM        EMPLOYEE2; This means the words 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' are default for ordered analytical functions.

SUM WINDOW group Function with PARTITION:

We use PARTITION BY clause to perform grouping. Following query provides partitioning based on department.   SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY DEPARTMENTNO ) FROM EMPLOYEE2; Employeeid DepartmentNo Salary Group Sum(Salary) 1 100 1000 15000 5 100 5000 15000 9 100 9000 15000 6 200 6000 30345.11 124 200 12345.11 30345.11 2 200 2000 30345.11 10 200 10000 30345.11 7 300 7000 21000 3 300 3000 21000 11 300 11000 21000 4 400 4000 36345.11 8 400 8000 36345.11 144 400 12345.11 36345.11 12 400 12000 36345.11 We can get similar result using the following syntax:   SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM EMPLOYEE2;   The Group Sum reflects the total for each department. The key words ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING are unnecessary since they are the default.