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

  • 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.

  • 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

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

  • 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

  • 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)

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

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