Ryan - Number of Member Renewal

# no of members - renewal

select a.*, ifnull((select sum(amount) from payments where state='normal' and a.mid=member_id and paid_type in ('card')),0) as payment

from
(select
  m.id as mid, m.email as email, m.grade as grade,
  date_format(convert_tz(m.created_at, '+00:00', '+8:00'), '%Y-%m-%d') as jd,
  (select dl.state from
(select id, member_id, dl.state from driver_licenses dl  group by id order by id desc)dl
where dl.member_id=mid
group by member_id
order by id desc
  ) as dlstate,
(select count(id) from reservations where state='completed' and m.id=member_id) as nuse
from members m
where m.imaginary in ('normal') group by mid)a
where dlstate in ('renewal')
#order by a.mid desc limit 100
;