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;