All accident info Rahil

SELECT r.id as rid,
r.member_id as mid,
cc.car_name "car_name",
cc.maker "manufacturer",
(YEAR(r.start_at) - YEAR(m.birthday)) as age,
ROUND(sum(ca.quotation_amount),0) "total_repair_cost",
count(c.registration_no) "number_of_accidents",
crs.name,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
IFNULL(sum(ra.mileage),0) as distance,
date(ca.accident_at+interval 8 hour ) as "accident date",
date(ca.created_at+ interval 8 hour) "GOS record created"
FROM car_accidents ca
LEFT JOIN reservations r ON r.id=ca.reservation_id
left join members m on m.id = r.member_id
left join reservation_appendixes ra on ra.reservation_id = r.id
LEFT JOIN cars c ON c.id=ca.car_id
LEFT JOIN car_classes cc ON cc.id=c.car_class_id
LEFT JOIN car_repair_shops crs ON crs.id=ca.car_repair_shop_id
WHERE ca.state <> "duplicated"
AND ca.state <> "deleted" and ((ca.insurance_type<>"thirdParty" and ca.is_insurance=1) or ca.is_insurance=0)
AND greatest(date(r.occupy_start_at+interval 8 hour),date(ca.accident_at+interval 8 hour))>= '2017-01-01'
AND greatest(date(r.occupy_start_at+interval 8 hour), date(ca.accident_at+interval 8 hour))<= '2020-05-01'
GROUP BY r.id
ORDER BY avg(ca.quotation_amount) DESC