select bpa as pay_ability
       ,concat(round(100*sum(aod)/count(*),1),'%') as overdue_ratio
from(
select a.agreement_id as aai
       ,a.customer_id as aci
       ,b.pay_ability as bpa
       ,case when a.overdue_days is null then 0 else 1 end as aod
from loan_tb a
left join 
customer_tb b
on a.customer_id = b.customer_id
) c
GROUP by c.bpa
order by sum(aod)/count(*) desc;