CODEWITHSUNDEEP
mysqlsqlsqlite
steve0nz
steve0nz

Reputation: 906

Find the Average salary deviation for an employee within department

I have a list of employees who work within a set of teams. We are trying to figure out for each year the difference for each employee’s salary to their department’s average. I have been looking at standard deviations but I’m a little stuck if I need to have a sub select to achieve this.

The table looks like:

enter image description here

I do not have any code to show because I always get stuck on aliases.

Upvotes: 0

Views: 292

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT T2.EmployeeID,
       T1.TeamID,
       T1.Salary,
       T1.AvgSalary,
       T2.Salary-T1.AvgSalary AS diff 
FROM
    ((SELECT TeamID,AVG(Salary) as AvgSalary,Year
    FROM TableName
    GROUP BY TeamID,Year) T1 JOIN
    (SELECT EmployeeID,TeamID,Salary
    FROM TableName) T2 ON T1.TeamID=T2.TeamID)
GROUP BY T2.EmployeeID,T1.TeamID,T2.Salary

Sample Result:

EmployeeID  TeamID  Salary  AvgSalary   diff
1           20      39999   28999       11000
2           22      44999   44999       0
3           20      23999   28999       -5000
4           20      34499   28999       5500
5           20      39559   28999       10560
6           20      33999   28999       5000

Sample result in SQL Fiddle.

Upvotes: 0

cha
cha

Reputation: 10411

Just break down your task in simple tasks. Let's find the average salaries first:

Select TeamID, `Year`, AVG(Salary) avgSal
FROM Employees
GROUP BY TeamID, `Year`

Now, to get the difference all you need to do is to subtract the employee's Salary from the average:

SELECT e.EmployeeID, e.Salary, a.avgSal, e.Salary - a.avgSal as diff
FROM Employees e
INNER JOIN 
(
    Select TeamID, `Year`, AVG(Salary) avgSal
    FROM Employees
    GROUP BY TeamID, `Year`
) as a
ON a.TeamID = e.TeamID and a.`Year` = e.`Year`

Upvotes: 1

Related Questions