SQL Server – Resolving SUM Function and Grouping Issues

sql serversum

I am running into an issue where using the Sum() function is not working as it should be. I am running this query. 

SELECT        tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup 
CROSS JOIN    tServiceJobCodes 
INNER JOIN    tServiceHours 
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode 
INNER JOIN    tServiceReports 
ON            tServiceHours.ReportNo = tServiceReports.ReportNo 
INNER JOIN    tServiceReps 
ON            tservicereports.employee = tservicereps.repid 
INNER JOIN    tRegions 
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
order by      EmployeeNumber asc

Gives the below output 

EmployeeNumber  JobCode                 Date           Hours
10245             82         2017-10-03 00:00:00.000    0.50
10245             82         2017-10-03 00:00:00.000    1.50
10245            215         2017-10-03 00:00:00.000    2.50
10245            215         2017-10-03 00:00:00.000    3.00

When I add the sum() to hours I get weird results back.

SELECT        tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              sum(tServiceHours.Hours) as Hourz
FROM          AdjustUserRoleRequestStatusLookup 
CROSS JOIN    tServiceJobCodes 
INNER JOIN    tServiceHours 
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode 
INNER JOIN    tServiceReports 
ON            tServiceHours.ReportNo = tServiceReports.ReportNo 
INNER JOIN    tServiceReps 
ON            tservicereports.employee = tservicereps.repid 
INNER JOIN    tRegions 
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
order by      EmployeeNumber asc

I get the below faulty output

EmployeeNumber  JobCode            Date          Hourz
10245             82    2017-10-03 00:00:00.000  17.50
10245            215    2017-10-03 00:00:00.000  27.50

from what I gather, the grouping is not working properly, which is probably what is causing the Sum() to go haywire. Any help is appreciated.

Best Answer

Use your main query as a subquery:

SELECT EmployeeNumber,
       JobCode,
       [Date],
       SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup 
CROSS JOIN    tServiceJobCodes 
INNER JOIN    tServiceHours 
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode 
INNER JOIN    tServiceReports 
ON            tServiceHours.ReportNo = tServiceReports.ReportNo 
INNER JOIN    tServiceReps 
ON            tservicereports.employee = tservicereps.repid 
INNER JOIN    tRegions 
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];
Related Question