Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Is There a Way to Calculate An Average Like This?

    Hi guys,

    I am needing to create a calculated field that will return the average of each employee's top three salaries in their salary history table.

    Is there a way to do this? I can't figure out how to make SQL know which dollar amounts are the highest in order to use the AVG() function. Thanks.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Try this:

    Code:
    SELECT EmployeeID, AVG(DISTINCT Salary) AvgSalary
    FROM
     (SELECT EmployeeID, Salary,
      DENSE_RANK() OVER (PARTITION BY EmployeeID ORDER BY Salary DESC) rnk
      FROM SalaryHistory) t
     WHERE rnk <= 3
    GROUP BY EmployeeID ;
    Depending on your definition of "top three salaries" you may want to use RANK in place of DENSE_RANK and/or AVG(...) instead of AVG(DISTINCT ...)

  3. #3
    Join Date
    Jun 2005
    Posts
    79
    I'll take a stab at it.

    SELECT AVG(Salary) FROM (SELECT TOP 3 Salary FROM Salary_History WHERE EmployeeID = 1 ORDER BY Salary DESC) AS t1

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That will only work for a single employee, not "each" employee as the poster requested.
    If the poster is using SQL Server 2005 or above, dportas' CTE method is the appropriate solution. If using 2000, there are methods utilizing temporary tables or recursive joins which will accomplish the same thing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •