08-04-2014 09:26 AM
Im trying to obtain a summary and provide a total for each variable by rep. The problem im having is that this code still brings back all records, however the average and and totals are correct but it displays the same numeric for each record. Any idea why it wont display 1 row with the totals?
create table work.redemptions_summary as
repay_Type as Rep
count(*) as count,
avg (a.i_tv) as Ave_tv,
avg (a.bal_o) as Average_Bal,
avg (b.cii) as cii,
avg (b.deli) as del,
count(*) as red_vol,
sum(bal_out) as red_bal,
sum(case when Rep= 'Cap' then bal_out else 0 end) as cap,
sum(case when Rep= 'Int' then bal_out else 0 end) as int,
sum(case when Rep= 'Mixed' then bal_out else 0 end) as part,
when remaining_months <= 1 then 'End'
when remaining_months <= 12 then 'OP'
when arr_stage_code ne 'ZZ' then 'Ar'
else 'Rem' end as Red_rea
work.red as a
left join work.red_stats as b
on a.anm = b.aco
platform = 'um'
and Arr_stage_code not in ('4','5','6')
08-04-2014 09:45 AM
I would imagine that its to do with the case statement creating a unique value for each of the rows. You can try putting select distinct to start, and also remove the case statement. You should then get one row per by group.