[SQL] Query issues when I use SUM and group records in SQL Server

I have the following query in Sql Server where I am trying to sum the pay time of some records that I group by certain columns. However, the result shows 2 records that should be one. Here's the query:

SELECT ISNULL(E.EMPID,0) "EMPID", SUM(T.PAYTIME) "PAYTIME", PC.PAYTRANSCODEIDST ,
PC.PAYTRANSCODEIDOT, T.DispatchDate, ISNULL(CostCenterID, '10109') "CostCenterID"
FROM TimeRecord T, Employee E, Paycode PC, EmpType ET
WHERE E.EmpId = T.EmpId
AND PC.Paycode = T.Paycode
AND ET.EMPID = T.EMPID
AND ((ET.STARTDATE <= '2024-09-29')
AND ((ET.ENDDATE IS NULL) or (ET.ENDDATE >= '2024-09-29')))
AND E.EMPID = 3129 AND DISPATCHDATE = '2024-09-27'
GROUP BY E.EMPID, CostCenterID, PC.PAYTRANSCODEIDST, PC.PAYTRANSCODEIDOT, T.DISPATCHDATE
ORDER BY E.EmpId, CostCenterID, PC.PayTransCodeIDST, PC. PayTransCodeIDOT, T.DispatchDate


And here are the results:



As you can see, the first 2 records should be one with a bigger pay time. I also tried to use INNER JOIN, ISNULL and also COLAESCE for the PAYTRANS... columns, but it didn't work. If I remove the 2 PAYTRANS... columns from GROUP BY and SELECT, it works fine, but I need them. These 2 may also have NULL values in the database but I'm not sure this is what's causing the issue. Thank you.

Continue reading...