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