select
all_zones.year_creation,
all_zones.month_creation,
all_zones.week_creation,
all_zones.nom_courant,
count(all_zones.id_table)
from
(select
  z.zonepk as id_table,
  z.date_create,
  extract(YEAR from z.date_create) as year_creation,
  extract(month from z.date_create) as month_creation,
  extract(week from z.date_create) as week_creation,
  z.latitude,
  z.longitude,
  u."name",
  u.email,
  dp.nom_courant,
  ds.longitude as longitude_etab,
  ds.latitude as latitude_etab
from zones z
inner join zones_changes zc on z.zonepk = zc.zonefk
inner join zones_protocoles zp on zp.zonechangefk  = zc.zonechangepk
left join users u on u.userpk = z.userfk
left join users_structures us on us.userfk = u.userpk
left join dico_structures ds on ds.structurepk = us.structurefk
inner join dico_protocoles dp on dp.protocolepk = zp.protocolefk
where
u.email not in('vne5@yopmail.com')) all_zones
group by
all_zones.year_creation,
all_zones.month_creation,
all_zones.week_creation,
all_zones.nom_courant
