TeraData SUM OVER function - Calculating Grand Totals and Subtotals

WITH... BY is used to create subtotals.

WITH is used to create grand totals.

WITH..BY and WITH are both Teradata features and cannot produce relational result.

We can set similar result using SUM window functions:

Generating Sub totals using ANSI SUM window function and Teradata WITH..BY :

Following is how we generate subtotals using the WITH .. BY feature:

select departmentNo, Employeeid, Salary

FROM EMPLOYEE

WITH SUM(salary) BY departmentno;

 *** Query completed. 18 rows found. 3 columns returned.

 *** Total elapsed time was 1 second.

DepartmentNo   Employeeid      Salary

------------  -----------  ----------

         100            1     1000.00

         100            9     9000.00

         100            5     5000.00

                           ----------

              Sum(Salary)    15000.00

         200          124    12345.11

         200            2     2000.00

         200            6     6000.00

         200           10    10000.00

                           ----------

              Sum(Salary)    30345.11

         300            3     3000.00

         300            7     7000.00

         300           11    11000.00

                           ----------

              Sum(Salary)    21000.00

         400            8     8000.00

         400            4     4000.00

         400           12    12000.00

         400          144    12345.11

                           ----------

              Sum(Salary)    36345.11

Thus we get SUM of salaries per department. But this result is not relational and hence this query cannot be used from SQL assistant.

Following is how we make use of ANSI standard SUM OVER() function to get sub totals:

select

departmentNo,

Employeeid,

Salary,

SUM(salary) OVER( PARTITION BY departmentNo

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FROM EMPLOYEE

;

DepartmentNo	Employeeid	Salary	Group Sum(Salary)
100	1	1000	15000
100	5	5000	15000
100	9	9000	15000
200	124	12345.11	30345.11
200	2	2000	30345.11
200	6	6000	30345.11
200	10	10000	30345.11
300	3	3000	21000
300	7	7000	21000
300	11	11000	21000
400	4	4000	36345.11
400	8	8000	36345.11
400	12	12000	36345.11
400	144	12345.11	36345.11

Each row gets the count of its group.

The PARTITION BY clause does the work of creating groups, same as what WITH BY does.

SUM window functions and other aggregate functions cannot be combined with normal aggregate functions.

Generating Grand Totals using ANSI SUM window function and Teradata WITH:

Following is how we generate grand totals using Teradata 'WITH' functionality:

select

DepartmentNo,

Employeeid,

Salary

FROM EMPLOYEE

WITH SUM(salary);

 *** Query completed. 15 rows found. 3 columns returned.

 *** Total elapsed time was 1 second.

DepartmentNo   Employeeid      Salary

------------  -----------  ----------

         200          124    12345.11

         400            8     8000.00

         200            2     2000.00

         200            6     6000.00

         400            4     4000.00

         400           12    12000.00

         100            1     1000.00

         300            3     3000.00

         200           10    10000.00

         300            7     7000.00

         300           11    11000.00

         100            9     9000.00

         100            5     5000.00

         400          144    12345.11

                           ----------

              Sum(Salary)   102690.22

Following is how we get a similar result using the ANSI standard SUM OVER() function:

select

departmentNo,

Employeeid,

Salary,SUM(salary) OVER(

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FROM EMPLOYEE

;

DepartmentNo	Employeeid	Salary	Group Sum(Salary)
100	1	1000	102690.2
100	5	5000	102690.2
100	9	9000	102690.2
200	2	2000	102690.2
200	6	6000	102690.2
200	10	10000	102690.2
200	124	12345.11	102690.2
300	3	3000	102690.2
300	7	7000	102690.2
300	11	11000	102690.2
400	4	4000	102690.2
400	8	8000	102690.2
400	12	12000	102690.2
400	144	12345.11	102690.2