nombre de patient par club

select name as hospital_name, concat(city_code,"/",hospital_code) as hospital_code, sum(club_type=1) as mother, sum(club_type=2) as 9_12, sum(club_type=3) as 13_17, sum(club_type=4) as jeune from (
select club_type, id_hospital from session
left join club_session on club_session.id=session.id_club_session
left join club on club.id=club_session.id_club
where is_present=1 and date between '2016-10-01' and now()
group by id_patient
)v
left join lookup_hospital on lookup_hospital.id=id_hospital
group by id_hospital