Driving Licence Auto Vs manual Reject - Rahil

set @start := '2019-09-01 00:00';
select
Month(A.date) as date,
count(distinct case when A.state not in ('noInput','null') then A.mid end) DocUploaded,
count(distinct case when A.type in ('auto','any') and A.state = 'approved' then A.mid end) as Auto_Approved,
count(distinct case when A.type in ('auto','any') and A.state = 'reject' then A.mid end) as Auto_reject,
count(distinct case when A.type in ('manual') and A.state = 'approved' then A.mid end) as Manual_Approved,
count(distinct case when A.type in ('manual') and A.state = 'reject' then A.mid end) as Manual_reject,
(count(distinct case when A.type in ('auto','any') and A.state = 'reject' then A.mid end)/count(distinct case when A.type in ('auto','any') and A.state = 'approved' then A.mid end))*100 as PCT_AUTO_Approved_Reject,
(count(distinct case when A.type in ('manual') and A.state = 'reject' then A.mid end)/count(distinct case when A.type in ('manual') and A.state = 'approved' then A.mid end)*100) as PCT_Manual_Approved_Reject

from
(select distinct dl.member_id as mid, min(dl.id) as dlid, dl.approval_type as type, dl.state as state , Date(dl.created_at + interval 8 hour) as date
from driver_licenses dl left join members m on m.id = dl.member_id
where dl.created_at + Interval 8 hour >= @start
and m.imaginary = 'normal'
group by 1
order by 1 desc) A
group by 1 desc