SQL Query to find all the employee whose salary sum is 80% of sum of salary of all employee.

Question

Post reply

SQL Query to find all the employee whose salary sum is 80% of sum of salary of all employee.

akash_singh

Ten Centuries

Points: 1089
More actions
August 14, 2014 at 5:42 am

#289910

Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket. I hope question is clear. Please let me know if anything else is required.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

For such questions, it would be nice if you provided table DDL, sample data and desired output.

See the first link in my signature on how to do this.

CREATE TABLE [dbo].[Salaries](

[ID] [int] IDENTITY(1,1) NOT NULL,

[EmployeeID] [int] NOT NULL,

[Salary] [numeric](18, 2) NOT NULL

);

GO

INSERT INTO dbo.Salaries([EmployeeID],[Salary])

VALUES (1,1000)

,(2,100)

,(3,250)

,(4,850)

,(5,1150)

,(6,600)

,(7,950)

,(8,300)

,(9,1500)

,(10,300)

,(11,250)

,(12,900)

,(13,850)

,(14,200)

,(15,800);

DECLARE @TotalSum NUMERIC(18,2);

SELECT @TotalSum = SUM(Salary)

FROM [dbo].[Salaries];

SELECT [EmployeeID], Salary, PercentageOfTotal

FROM

(

SELECT [EmployeeID], Salary, PercentageOfTotal = IIF(@TotalSum = 0,0,SUM(Salary) OVER (ORDER BY Salary DESC) / @TotalSum)

FROM [dbo].[Salaries]

) tmp

WHERE PercentageOfTotal <= 0.8;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

akash_singh Ten Centuries Points: 1089 More actions · Answer 2

Can you please give solution in SQL 2008 as well as IIF does not work in SQL 2008.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

akash_singh (8/14/2014)
Can you please give solution in SQL 2008 as well as IIF does not work in SQL 2008.

IIF is shorthand for a CASE statement. I'm sure you can figure out how to replace it.

(by the way, this is a SQL Server 2012 forum)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

akash_singh (8/14/2014)
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket. I hope question is clear. Please let me know if anything else is required.

How many employees and how often do you need to do this? I ask because there aren't many good (horrible, horrible performance or some nasty trick code to get performance) options in 2008 although there are some easy ones. We need to know so we can figure out the "best" option for you.

I share the sentiment. It would be nice if you had provided some test data and posted to the correct forum. 😉 See the first link in my signature line under "Useful Links" to learn how to create readily consumable test data in a hurry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)