SQL - How can I get the average employee salary for each department?

Question

This is the UML for the tables: https://dev.mysql.com/doc/employee/en/sakila-structure.html

This my attempt:

---

SELECT
    CONCAT(employees.first_name, ' ', employees.last_name) AS 'EmployeeName',
    salaries.emp_no AS 'Employee Number',
    departments.dept_no AS 'Department Number',
    departments.dept_name AS 'Department name',
    AVG(salaries.salary) AS 'Average salary'
FROM salaries
INNER JOIN dept_emp
    ON salaries.emp_no = dept_emp.emp_no
INNER JOIN employees
    ON salaries.emp_no = employees.emp_no
INNER JOIN departments
    ON dept_emp.dept_no = departments.dept_no
GROUP BY    salaries.emp_no,
            dept_emp.dept_no

---

I just get the average employee salary for all departments a person worked in. My desired result must group by each employee for each department. Any help is appreciated.

Solution 1

you can try this query

SELECT
    CONCAT(employees.first_name, ' ', employees.last_name) AS 'EmployeeName',
    salaries.emp_no AS 'Employee Number',
    departments.dept_no AS 'Department Number',
    departments.dept_name AS 'Department name',
    Cte_DepartmentSalaries.AvgSalary AS 'Average Salary'

--AVG(salaries.salary) AS 'Average salary'

FROM salaries
INNER JOIN dept_emp
    ON salaries.emp_no = dept_emp.emp_no
INNER JOIN employees
    ON salaries.emp_no = employees.emp_no
INNER JOIN departments
    ON dept_emp.dept_no = departments.dept_no   
LEFT JOIN (SELECT
    departments.dept_no,
    departments.dept_name,
    AVG(Salaries.Salary) AS AvgSalary
FROM Salaries
INNER JOIN dept_emp
    ON salaries.emp_no = dept_emp.emp_no
INNER JOIN departments
    ON dept_emp.dept_no = departments.dept_no
GROUP BY    departments.dept_no,
            departments.dept_name) AS Cte_DepartmentSalaries
    ON dept_emp.dept_no = Cte_DepartmentSalaries.dept_no

Solution 2

if you join to salaries where to_date between from and to date you will get any changes in the salary over those time periods.

SELECT  CONCAT(e.first_name,' ',e.last_name) AS 'EmployeeName',
        s.emp_no AS 'Employee Number',
        d.dept_no AS 'Department Number',
        d.dept_name AS 'Department name',
        AVG(s.salaries.salary) AS 'Average salary'
FROM    employees e
        JOIN dept_emp de ON e.emp_no = de.emp_no
        JOIN salaries s ON s.emp_no = de.emp_no
                           AND (de.to_date >= s.from_date AND de.to_date <= s.to_date)
        JOIN departments d ON d.dept_no = de.dept_no
GROUP BY s.emp_no,
        d.dept_no

Comments

[JamieD77]

problem is that the salary is duplicated for each department because there is no link from dept_no to salary.. grouping by more than just emp_no and dept_no won't solve anything in this case i believe

[Hogan]

@JamieD77 - lets wait for the OP to try this and say it works.

[petrojet]

i couldn't execute that query i using mySQL not oracle

[Hogan]

@JamieD77 - I see what you mean -- he will have to include the dates in the join too I guess -- if that doesn't work then his model is FUBAR

[petrojet]

give me the same problem

[Hogan]

@abdelhamidahmed -- you have to join salery by date to department table -- how does you model work? as shown it is an impossible problem to solve

[Hogan]

@abdelhamidahmed - see the edit about the join change.

[Kannan Kandasamy]

Updated query by not using common table expressions... can you check now?

[spencer7593]

I think the comparison of s.to_date should be to de.from_date, not de.to_date. This excludes any rows that have to_date as NULL, and also reports as the "average" the average of the values of salary, without regard to the length of time at that salary. As a simple example, consider an employee works in a department for three years with a salary of 10,000, and then one year with a salary of 14,000. Would the average salary be 12,000? Or would it be 11,000?

[JamieD77]

@spencer7593 what if you just coalesce both to_date fields to current date

[spencer7593]

The "average salary" returned by this query is for a given department, but includes all salary for every employee that ever worked in the given department, including salary when that employee worked in other departments. This must be the result OP is after (given that this is the selected answer). But it's very strange to name this result "average employee salary for each department", and to give the description as "get average salary for each employee per each department he worked in". This average is better described "all salary history of every employee that ever worked in a department"

[Jonathan Willcock]

Thank you for your answer. However wehen answering please read the question carefully. The OP wanted average by department

[ModulusJoe]

This won't work unfortunately as salary is not a column in the employees table, At a minimum you would have to left join salaries, but with the assumption that salaries change over time, it's not as simple as a single join.