CODEWITHSUNDEEP
sqlms-access
erkruns
erkruns

Reputation: 3

SQL SUM() function not working properly with multiple tables in query

I am trying to pull the sum of hours worked worked and compare it to the sum of hours paid for each individual employee. These are stored in two different tables. When I query the tables separately into 2 different tables they work perfect. When I place them in the same query the results are way off.

Sample Data-PayrollTransactions: PayrollTransactions

enter image description here

PayrollTime

enter image description here

This is the query that does not work:

SELECT Emp_No, Sum(Regular_Hours) AS PaidRegHours, Sum(Overtime_Hours) AS PaidOTHours, Sum(Reg_Hours) AS ClockedRegHours, Sum(OT_Hrs) AS ClockedOTHours
FROM PayrollTransactions, PayrollTime
WHERE Employee_No = Emp_No
GROUP BY Emp_No;

The result it pulls for 1 employee is 1000 PaidRegHours. When doing a query just from PayrollTransactions as such:

SELECT Employee_No, Sum(Regular_Hours) AS PaidRegHours, Sum(Overtime_Hours) AS PaidOTHours
FROM PayrollTransactions
GROUP BY Employee_No;

the result for that same employee is 200 PaidRegHours, which is correct. This same problem exists for all my computed fields. I am unsure how to fix this problem. Thanks for your help!

Desired Results: DesiredOutput

enter image description here

Upvotes: 0

Views: 398

Answers (1)

Parfait
Parfait

Reputation: 107567

Classic problem of JOIN multiples. By querying these two tables that share a many-to-many relationship on employee, you return multiple pairings (i.e., duplicates, triplets, quadruples) that are then aggregated, turning actual 200 to 1,000 summed hours. Instead, consider joining one-to-one pairs which can be achieved by joining aggregates of both tables.

Below uses subqueries but can also use stored queries. Also, the explicit JOIN is used (current ANSI SQL standard) and not implicit join as you currently have with WHERE.

SELECT p.Employee_No, p.PaidRegHours, p.PaidOTHours, t.ClockedRegHours, t.ClockedOTHours
FROM
   (SELECT Employee_No, 
           Sum(Regular_Hours) AS PaidRegHours, 
           Sum(Overtime_Hours) AS PaidOTHours
    FROM PayrollTransactions
    GROUP BY Employee_No) p
INNER JOIN
   (SELECT Emp_No, 
           Sum(Reg_Hours) AS ClockedRegHours, 
           Sum(OT_Hrs) AS ClockedOTHours
    FROM PayrollTime
    GROUP BY Emp_No) t
  ON p.Employee_No = t.Emp_No

Alternatively, with stored queries which sometimes can be more efficient with Access' engine:

SELECT p.Employee_No, p.PaidRegHours, p.PaidOTHours, t.ClockedRegHours, t.ClockedOTHours
FROM qryPayrollTransactionsAgg p
INNER JOIN qryPayrollTimeAgg t
  ON p.Employee_No = t.Emp_No

Upvotes: 1

Related Questions