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

Teradata COUNT window Function - Generating totals and subtotals

We have learnt using WITH and WITH BY functions earlier.

• WITH BY is used to create subtotals and >

• WITH is used to create grand totals.

BUT problem with using these functions is that WITH and WITHBY are both Teradata extensions . They are not ANSI standards.

Also we know that we cannot use WITH and WITH BY to get relational result.

Hence we make use of COUNT window functions to create subtotals or grand totals.

COUNT window is the ANSI standard.

Generating SUBTOTALs-- Using WITH ...BY and using ANSI Standard COUNT function

Following is the Teradata way of generating the subtotals using the WITH..BY

select departmentNo, Employeeid, Salary

FROM EMPLOYEE

WITH COUNT(departmentNo) 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

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

           3

         200          124    12345.11

         200            2     2000.00

         200            6     6000.00

         200           10    10000.00

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

           4

         300            3     3000.00

         300            7     7000.00

         300           11    11000.00

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

           3

         400            8     8000.00

         400            4     4000.00

         400           12    12000.00

         400          144    12345.11

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

           4

The result is non relational and hence this query cannot be run using SQL assistant.

We need to run this query using BTEQ.

We can get the similar result using ANSI standard COUNT OVER function.

select

departmentNo,

Employeeid,

Salary,

COUNT(*) OVER (PARTITION BY departmentNO

ROWS BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING)

FROM EMPLOYEE

;

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

Note that this Output is relational and can be run in SQL assistant.

What does the PARTITION BY do?

The 'PARTITION BY ' clause defines the group in which the rows are to be counted.

It does the same function that 'BY' clause does in the teradata way of getting subtotals-  .i.e Establishing Groups.

The count gets showed in each row within a group and hence appear multiple times.

COUNT Window function and standard aggregate functions (.ie SUM,AVG,COUNT,MIN,MAX) may not be used in same select.

Generating Grand TOTALs-- Using WITH ...BY and using ANSI Standard COUNT function

Following is the Teradata Way of generating grand totals using WITH

select

departmentNo,

Employeeid,

Salary

FROM EMPLOYEE

WITH COUNT(departmentNo);

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

 *** Total elapsed time was one minute and 4 seconds.

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

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

          14

The result is non relational and hence this query cannot be run using SQL assistant.

We need to run this query using BTEQ.

We can get the similar result using ANSI standard COUNT OVER function.

select

departmentNo,

Employeeid,

Salary,

COUNT(*) OVER(

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FROM EMPLOYEE

;

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

As we can see the total count is output on each row.