sqladvanced15 minutes

Fix the Aggregation Bug in Employee Salary Report Query

An SQL query intended to generate a report summarizing total salaries by department and job title has incorrect aggregation logic causing wrong results. Your task is to identify and fix the bugs in the query to produce accurate aggregation.

Challenge prompt

Given the following broken SQL query that aims to calculate total salaries grouped by department and job title from the Employees table, identify the bug(s) and correct the query so it produces the correct aggregated sums for each department and job title combination.

Guidance

• Check for missing or incorrect GROUP BY clauses that affect aggregation behavior.
• Review JOIN conditions and SELECT columns to ensure consistent grouping.
• Validate that aggregate functions like SUM() include only relevant columns.

Hints

• Look for columns in the SELECT clause that are neither aggregated nor included in GROUP BY.
• Check if any JOINs cause row multiplication affecting totals.
• Consider using GROUP BY on all non-aggregated columns to fix issues.

Starter code

SELECT d.department_name, e.job_title, SUM(e.salary) total_salary
FROM Employees e
JOIN Departments d ON e.department_id = d.id
WHERE e.hire_date > '2020-01-01'
ORDER BY d.department_name, e.job_title;

Expected output

A result table with columns: department_name, job_title, total_salary, where total_salary is the sum of salary for each department-job_title group accurately aggregated.

Core concepts

SQL aggregationGROUP BY clauseJOIN operationsAggregate functions

Challenge a Friend

Send this duel to someone else and see if they can solve it.