Blog Archive

Sunday 21 April 2013

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

No comments:

Post a Comment