Finding Individual contribution using SUM OVER (SUM window)

Finding Individual contribution using SUM OVER:

In the employee table we have individual salaries. However we may need to find out what percentage of total salary is an individual salary.

Following query would help us get that result:

select

employeeid,

DepartmentNo,

salary,

SUM(salary) over (PATITION BY departmentNo rows between unbounded preceding and unbounded following) as totsum,

(salary * 100.00 /totsum)

from employee;

Employeeid	Salary	totsum	((Salary*100.00)/totsum)
1	1000	102690.2	0.9738
2	2000	102690.2	1.9476
3	3000	102690.2	2.9214
4	4000	102690.2	3.8952
5	5000	102690.2	4.869
6	6000	102690.2	5.8428
7	7000	102690.2	6.8166
8	8000	102690.2	7.7904
9	9000	102690.2	8.7642
10	10000	102690.2	9.738
11	11000	102690.2	10.7118
12	12000	102690.2	11.6856
124	12345.11	102690.2	12.0217
144	12345.11	102690.2	12.0217

Thus above result tells us that salary of employee 124 is actually 12.02% of the total salaries.

We can use the concept to find how percentage of departmental salary is the individual salary.

select

employeeid,

DepartmentNo,

salary,

SUM(salary) over (PARTITION BY departmentNo rows between unbounded preceding and unbounded following) as totsum,

(salary * 100.00 /totsum)

from employee;

Employeeid	DepartmentNo	Salary	totsum	((Salary*100.00)/totsum)
1	100	1000	15000	6.6667
9	100	9000	15000	60
5	100	5000	15000	33.3333
10	200	10000	30345.11	32.9542
124	200	12345.11	30345.11	40.6824
6	200	6000	30345.11	19.7725
2	200	2000	30345.11	6.5908
3	300	3000	21000	14.2857
7	300	7000	21000	33.3333
11	300	11000	21000	52.381
4	400	4000	36345.11	11.0056
144	400	12345.11	36345.11	33.9664
8	400	8000	36345.11	22.0112
12	400	12000	36345.11	33.0168

From the above result we get information that salary of employee 5 is 33.333% of the departmental salary   .ie sum of salary of department 100 .ie 15000

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