I am working on a query and trying to get to summarize total_amount but still don't get the right group by, it is not summarizing by group. Like PYGRP has all SA3 and its not group and I get too many rows I think my query needs a tweak and I think my SUM is not in the right place.
There should only be one SA3 with total_amount with one company, one Cd, one week_nbr and so on..
Select sum(al_amount) as TOTAL_AMOUNT, PAYGP,COMPANY, CD,Week_nbr, Payroll
from CHECKS B
inner join JOB JO ON JO.PAYGP=B.PAYGP
and CD IN ('401','40A','40P','410','411')
AND ENTRY_NBR <>0
AND JO.FDT=(SELECT MAX(FDT)
group by b.PAYGP,company, CD, b.EMPLID, week_nbr, Payroll