SQL Puzzle | The Salary Share Puzzle

In this puzzle you have to find each department’s salary share with respect to the organizations total salary payout

Please check out the sample input and the expected output below-

Sample Input

Expected Output

Rules/Restrictions

• The solution should be should use SINGLE “SELECT” statement.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--



CREATE TABLE SalaryDistributionAcrossDept
(	
	 EmpId INT
	,EmpName VARCHAR(1)
	,DeptNo INT
	,EmpSal FLOAT
)
GO

INSERT INTO SalaryDistributionAcrossDept VALUES
(1,'A',101,1200),
(2,'B',101,1200),
(3,'C',102,1000),
(4,'D',102,7000),
(5,'E',102,2000),
(6,'F',102,6000),
(7,'G',102,6500),
(8,'H',103,2300),
(9,'I',104,3200),
(10,'J',104,2100),
(11,'K',105,3400),
(12,'L',105,2000),
(13,'M',105,3500)
GO


--

Solution 1 | Ranking Functions

--



SELECT DISTINCT 
		DeptNo
				
		,ROUND ( ( SUM(EmpSal)OVER(PARTITION BY DeptNo) / SUM(EmpSal)OVER(PARTITION BY 1) ) * 100 , 2)  
                "% Share"
FROM 
		SalaryDistributionAcrossDept

ORDER BY DeptNo


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com