Charges Breakdown by reservations - For Socar Points

select p.reservation_id as prid,p.member_id,r.way,
p.state,
p.type ,
p.point,
date(p.created_at + interval 8 hour) as created_date,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','toll','penalty','cancelFee','etc','accident','refund',
'fine','reservationFee','returnFee', 'accidentOpponent', 'parkingFee')),0)),2) as totalcharges,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent')),0)),2) as rent,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('d2d')),0)),2) as d2d,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('mileage')),0)),2) as mileage,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('toll')),0)),2) as toll,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('penalty')),0)),2) as penalty,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('cancelFee')),0)),2) as cancelFee,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('etc')),0)),2) as etc,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('accident')),0)),2) as accident,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('refund')),0)),2) as refund,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('fine')),0)),2) as fine,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('reservationFee')),0)),2) as reservationFee,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('returnFee')),0)),2) as returnFee,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('accidentOpponent')),0)),2) as accidentOpponent,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('parkingFee')),0)),2) as parkingFee
from points p left join reservations r on r.id = p.reservation_id
where p.type = 'redeem'
and p.state = 'normal'
and year(p.created_at + interval 8 hour) = '2019'
group by prid