USE caris_db;
SET @start_date="2017-10-01";
SET @end_date="2018-03-31";

select left(patient_code,8), count(*) from
(select distinct patient_code #left(patient_code,8), count(*) 
from session 
left join club_session on club_session.id=id_club_session
left join club on club.id=id_club
#left join club_patient on club_patient.id_club=club.id
left join patient on patient.id=id_patient
where is_present=1 and club_type=1 and left(patient_code,8) is not null and date between @start_date and @end_date
)f
group by left(patient_code,8)