abdelhamid ahmed abdelhamid ahmed - 2 months ago 16
MySQL Question

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

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

enter image description here

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.

Answer

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
Comments