Fix the Incorrect JOIN Condition in Employee Salary Query
Identify and fix the bug in the provided SQL query that calculates the total salary by department. The current query returns incorrect totals due to a faulty JOIN condition.
Challenge prompt
You have two tables: Employees(emp_id, emp_name, dept_id, salary) and Departments(dept_id, dept_name). The goal is to write a query that returns each department name along with the total salary of its employees. The provided query is intended to do this but returns incorrect results. Fix the bug in the query so it works correctly.
Guidance
- • Check the JOIN condition carefully to ensure employees are matched to their correct department.
- • Verify that aggregations (SUM) are done correctly grouped by department.
- • Test the query after fixing it to confirm results are as expected.
Hints
- • The ON clause might be joining columns incorrectly or using wrong table aliases.
- • Ensure the GROUP BY clause correctly references the grouped columns.
- • Check for typos or misuse of columns in either SELECT or JOIN parts.
Starter code
SELECT d.dept_name, SUM(e.salary) AS total_salary
FROM Employees e
JOIN Departments d ON e.emp_id = d.dept_id
GROUP BY d.dept_name;Expected output
A list of department names each with the sum of all employee salaries in that department, e.g. | dept_name | total_salary | |-------------|--------------| | Sales | 150000 | | Engineering | 250000 | | HR | 90000 |
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.