Problem with Group by and Sum

• shezi

  SSCommitted

  Points: 1514

  More actions

  August 14, 2017 at 11:24 am

  #402424

  I need my query to be reorganized or fixed. It is not properly calculating the total hours for "UREG" when i do group by. I know it has to do with the travel column and the sub query doesn't account for that. if my travel column is all marked the same then it does the calculation correctly. Maybe I have to pivot or partition instead of doing it by sub queries. Please advise
  
SELECT employee_no,
   timecategoryid AS TimeCategoryID,
   travel,
   UREG,
     REG
FROM (SELECT E.employee_no,
     PR1.pay_period_id,
     PR1.timecategoryid,
     PR1.travel,
     (SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
      WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 0) and (rate_type = 'REG')
                ) AS UREG,
                (SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
      WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 1) and (rate_type = 'REG')
                ) AS REG
   FROM dbo.employee AS E
     INNER JOIN dbo.pay_records AS PR1
        ON PR1.employee_no = E.employee_no
   GROUP BY E.employee_no,
      PR1.travel,
      PR1.pay_period_id,
      PR1.timecategoryid) AS X
WHERE employee_no = '327'
   AND pay_period_id = 16
GROUP BY employee_no,
    timecategoryid,
    travel,
    UREG,
         REG

  

  It shoud have been this


  Under UREG where travel is marked false the sum of hours for the timecategoryID should have been 5,  and where it is marked as true it should have been 4hrs.  if I mark travel as both false/true then I get the correct count of 9 "UREF" hours.

  Below is the snap shot of the query of the pay records table where you can see the breakdown of hours.
  
Select pr.pay_period_id,employee_no, pr.TimeCategoryID, pr.hours, pr.Travel , pr.rate_type
from Pay_Records pr
where pay_period_id=16 and employee_no='327'

  


• drew.allen

  SSC Guru

  Points: 76986

  More actions

  August 14, 2017 at 3:10 pm

  #1955226

  Pictures of your data do not help.  Please post scripts of your sample data as outlined in the first link in my signature.

  You're sample data does not include the valid_payroll field.  My guess is that the valid_payroll field is 0 for all records for this employee in this pay period.  Perhaps you meant to filter on Travel rather than valid_payroll.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  August 14, 2017 at 4:15 pm

  #1955235

  I think (but can't be sure without seeing the data) is that you'd want to add an extra clause to your WHERE in the nested selects.  You would want to add in
  AND Travel = PR1.Travel
  If I am eyeballing that right, I think that is all that needs to be added.  So for UREG, you'd have this instead:
  (SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
  WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 0) and (rate_type = 'REG')  AND (Travel=PR1.Travel)
      ) AS UREG,


  But without real data to test it with, I don't know for sure.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.