abdelhamid ahmed abdelhamid ahmed - 2 months ago 11
MySQL Question

SQL - how i get 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

and 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 average employee salary for all departments he worked in it.
my query must group by each employee for each department
any help

[enter image description here][2]

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