Loui Query - Zones, cars - Dashboard -Daily Index File share

select cz.team, cz.region, cz.zid, cz.zname, cz.cid, cz.plate, cz.ctype, cz.op_day,
ifnull(sum(rm.nuse),0) as nuse, ifnull(sum(rm.rnd),0) as round, ifnull(sum(rm.d2d),0) as d2d, ifnull(sum(rm.oneway),0) as oneway, ifnull(sum(rm.onewayreturn),0) as onewayreturn,
ifnull(sum(rm.dur),0) as dur,  ifnull(sum(rm.nmember),0) as memeber, ifnull(sum(rm.NR),0) as NR#, (ifnull(sum(rm.rent),0)) - (ifnull(sum(rm.paid_coupon),0)) as rn

from
(select cz.zone_id as zid, z.region as region, z.city as team, z.name as zname, cz.car_id as cid, c.registration_no as plate, c.car_class_id as class, (select car_classes.car_name from car_classes where car_classes.id=c.car_class_id) as ctype, count(cz.id) as op_day,
date_sub(cz.log_date, interval 1 day) as czdate
from car_zone_logs cz, zones z, cars c
where cz.zone_id=z.id
and cz.car_id=c.id
and z.id not in (2,3,101,383,378,697,712,818)
and date_sub(cz.log_date+interval 8 hour, interval 1 day) between ('2019-02-01 00:00:00') and ('2019-04-31 23:59:59')
group by cid, zid
order by cid
)cz
left join
(select
 r.car_id as cid2,
 r.start_zone_id as zid2,
 count(r.id) as nuse,
 count(if(r.way='round', r.id, null)) as rnd,
 count(if(r.way='d2d', r.id, null)) as d2d,
 count(if(r.way='oneway', r.id, null)) as oneway,
 count(if(r.way='onewayreturn', r.id, null)) as onewayreturn,
 count(distinct r.member_id) as nmember,
 sum(timestampdiff(hour, r.start_at, r.end_at)) as dur,
 ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','mileage')),0) as rent,
 ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id and paid_type='coupon'),0) as paid_coupon,
 sum((ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0))) as NR,
 date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as czdate2
from reservations r, members m
where
r.member_id=m.id
and m.imaginary in ('normal','sofam')
and r.way in ('round','d2d','oneway')
and r.state = 'completed'
and r.return_at+interval 8 hour between  ('2019-02-01 00:00:00') and ('2019-04-31 23:59:59')
group by cid2, zid2, czdate2
order by cid2
) rm
on ( cz.zid=rm.zid2 and cz.cid=rm.cid2)
group by cz.zid, cz.cid
order by cz.zid, op_day desc, cz.cid