Odd GROUP BY output DB2 - Results not as expected

Question

If I run the following query:

select  load_cyc_num
,  crnt_dnlq_age_cde
,  sum(cc_min_pymt_amt) as min_pymt
,  sum(ec_tot_bal) as budget
,  case when ec_tot_bal > 0 then 'Y' else 'N' end as budget
,  case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end as arngmnt
,  sum(sn_close_bal) as st_bal
from  statements
where  (sn_close_bal > 0 or ec_tot_bal > 0)
and  load_cyc_num in (200911)
group by  load_cyc_num
,  crnt_dnlq_age_cde
,  case when ec_tot_bal > 0 then 'Y' else 'N' end 
,  case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end

then I get the correct "BUDGET" grouping, but not the correct "ARRANGEMENT" grouping, only two rows have a "Y".

If I change the order of the case statements in the GROUP BY, then I get the correct grouping (full Y-N breakdown for both columns).

Am I missing something obvious?

Answer 1

Try moving

, sum(cc_min_pymt_amt) as min_pymt, sum(ec_tot_bal) as budget

to the end of the select statement, i.e.

    select  load_cyc_num,  
crnt_dnlq_age_cde,  
case when ec_tot_bal > 0 then 'Y' else 'N' end as budget,  
case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end as arngmnt,  
sum(sn_close_bal) as st_bal, 
sum(cc_min_pymt_amt) as min_pymt,  
sum(ec_tot_bal) as budget
    from  statements
where  (sn_close_bal > 0 or ec_tot_bal > 0)and  load_cyc_num in (200911)
group by  load_cyc_num,  
crnt_dnlq_age_cde,  
case when ec_tot_bal > 0 then 'Y' else 'N' end ,  
case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end