This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the average percentage of shared salary by each employee department.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EmpId EmpName DeptID Salary ----------- ---------- ----------- ----------- 1 ABC 101 10000 2 XYZ 101 20000 3 FGH 102 25000 4 WER 102 30000 5 TYU 102 40000 6 QWE 103 10000 7 XCV 103 15000 8 ASD 104 18000 9 UIO 104 20000 10 JKL 104 35000 |
Expected Output:
|
1 2 3 4 5 6 |
DeptID DeptTotalSalary AllTotal SharedPercentage ----------- --------------- ----------- ---------------- 101 30000 223000 13 102 95000 223000 42 103 25000 223000 11 104 73000 223000 32 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE tbl_Emp ( EmpId INT ,EmpName VARCHAR(10) ,DeptID INT ,Salary INT ) GO INSERT INTO tbl_Emp VALUES (1,'ABC',101,10000) ,(2,'XYZ',101,20000) ,(3,'FGH',102,25000) ,(4,'WER',102,30000) ,(5,'TYU',102,40000) ,(6,'QWE',103,10000) ,(7,'XCV',103,15000) ,(8,'ASD',104,18000) ,(9,'UIO',104,20000) ,(10,'JKL',104,35000) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
;with ctetest as ( select DeptID, SUM(Salary) as DeptTotalSalary from tbl_Emp group by DeptID ) ,cte as ( select SUM(Salary) as AllTotal from tbl_Emp ) ,cte2 as ( select DeptID,DeptTotalSalary,AllTotal from ctetest as a cross apply ( select AllTotal from cte ) as t ) select *, round(((DeptTotalSalary *100))/AllTotal,2) as SharedPercentage from cte2 |
Please try the different solution for this puzzle and share it via comment...
