Fix the Bug in SQL Query to Correctly Calculate Employee Department Averages
You are given a SQL query that attempts to calculate the average salary per department along with the department name. However, the query is not returning correct results due to a bug in the JOIN and GROUP BY clauses. Your task is to identify and fix the bug to ensure the query returns each department's name along with the average salary of its employees.
Challenge prompt
The following SQL query aims to get the average salary of employees grouped by their department name. However, the output is either incorrect or throws an error. Fix the query to correctly return each department's name and the average employee salary for that department.
Guidance
- • Review the JOIN condition to ensure it matches the appropriate keys between the 'employees' and 'departments' tables.
- • Check the GROUP BY clause to verify that it includes all non-aggregated columns selected in the query.
- • Test the fixed query to make sure it returns one row per department with the correct average salary.
Hints
- • Remember that when using GROUP BY, all selected columns that are not part of an aggregate function must be included in the GROUP BY clause.
- • Ensure that the JOIN keys are properly specified, as mismatched keys could result in an empty or incorrect join.
Starter code
SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY e.department_id;Expected output
A result set with two columns: department_name and avg_salary, where each row corresponds to a department with the correct average salary of its employees.
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.