0

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.

  • 2
    Please tag your RDBMS – McNets Dec 29 '17 at 14:56
  • 2
    Change the GROU BY clause to be just GROUP BY tServiceReps.EmployeeNumber, tServiceHours.JobCode, tServiceReports.[Date] – Pieter Geerkens Dec 29 '17 at 15:00
  • McNets, whats RDBMS? Sorry new to the forum. – joshua ortiz Dec 29 '17 at 15:14
  • @joshuaortiz the DataBase Management System that you use (Oracle, Postgres, Firebird, DB2, Sybase, SQLite, ...). There are so many. It's probably SQL Server or Sybase, judging from the use of square brackets but we can't be sure. Please also add the version of it. – ypercubeᵀᴹ Dec 29 '17 at 15:45
  • @yper-trollᵀᴹ , it is ms sql. sorry for not including it. – joshua ortiz Dec 29 '17 at 15:48
3

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];
  • You can't have an order by in a CTE without a TOP, drunky ;) – Erik Darling Dec 29 '17 at 15:08
  • @sp_BlitzErik yep, it's true, – McNets Dec 29 '17 at 15:09
  • yep, I am half asleep and want to go back on Holiday! thanks for the help! – joshua ortiz Dec 29 '17 at 15:13
  • I'm glad to help – McNets Dec 29 '17 at 15:14
  • @McNets is there a way to have it return all null values for hours? for example: where hours LIKE '%' +@hours+ '% – joshua ortiz Dec 29 '17 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.