Amount spent by sofam - TIM

(select distinct r.member_id ,m.display_name as name,
count(r.id) as reservations,
sum(ra.mileage) as Distance,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as Duration,
round((timestampdiff(day, min(r.start_at), max(r.start_at)))) as Days_Active_on_socar,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)),2) as Total_Charges,
round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons_spent,
round(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)),2) as Net_paid_to_Socar,
Round(round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2)/round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)),2),2) as coupon_spent_PCT,
round(round(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)),2)/count(r.id) ,2) as Average_paid_spent_per_resv,
round((sum(ra.mileage)/round((timestampdiff(day, min(r.start_at), max(r.start_at))))),2) as Ratio_Dis_to_Daysactive
from reservations r, zones z, members m, reservation_appendixes ra
Where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and r.id = ra.reservation_id
    and (m.imaginary in ('sofam') or m.id IN (84450,75140))
    and r.state in ('completed')
group by r.member_id
order by duration desc)