select distinct 
TAB.structurepk,
TAB.nom_etablissement,
TAB.longitude,
TAB.latitude,
TAB.type_etablissement,
TAB.adress,
TAB.no_uai,
TAB.zipcode,
TAB.city,
TAB.academie,
TAB.Annee_scolaire,
SUM(+1) as "TOTAL",
SUM(CASE TAB.protocole WHEN 'Oiseaux des Jardins' THEN +1 ELSE +0 END) as "Oiseaux des Jardins",
SUM(CASE TAB.protocole WHEN 'Sauvages de ma rue' THEN +1 ELSE +0 END) as "sauvages de ma rue",
SUM(CASE TAB.protocole WHEN 'Opération escargots' THEN +1 ELSE +0 END) as "Opération Escargots",
--SUM(CASE TAB.protocole WHEN 'Escargots des Jardins (Inventaire)' THEN +1 ELSE +0 END) as "Opération Escargots inventaire",
SUM(CASE TAB.protocole WHEN 'Observatoire des Vers de Terre' THEN +1 ELSE +0 END) as "Placettes à vers de terre",
SUM(CASE TAB.protocole WHEN 'Biolit' THEN +1 ELSE +0 END) as "Biolit",
SUM(CASE TAB.protocole WHEN 'SPIPOLL' THEN +1 ELSE +0 END) as "SPIPOLL"
from (SELECT DISTINCT
      --observations.observationpk AS Num_observation,
      --observations.protocolefk,
      dico_protocoles.nom_courant as protocole,
      --observations.date AS Date_observation,
      groupes.groupepk as groupepk,
      groupes.anneescol AS Annee_scolaire,
      --groupes.niveau AS niveau,
      dico_structures.structurepk as structurepk,
      dico_structures.name AS nom_etablissement,
      dico_structures.longitude AS longitude,
      dico_structures.latitude AS latitude,
      dico_structures.type AS type_etablissement,
      dico_structures.address1 as adress,
      dico_structures.no_uai AS no_uai,
      dico_structures.zipcode AS zipcode,
      dico_structures.city AS city,
      dico_academies."name" as academie
      FROM observations
      LEFT JOIN observateurs ON observations.observateurfk = observateurs.observateurpk
      LEFT JOIN groupes ON groupes.groupepk = observateurs.groupefk
      LEFT JOIN users ON users.userpk=groupes.userfk
      LEFT JOIN dico_structures on dico_structures.structurepk = groupes.structurefk
      LEFT JOIN dico_academies on dico_academies.academiepk = dico_structures.academiefk
      LEFT JOIN observations_abondances on observations_abondances.observationfk =observations.observationpk
      left join dico_protocoles on dico_protocoles.protocolepk = observations.protocolefk) TAB
group by
TAB.structurepk,
TAB.nom_etablissement,
TAB.longitude,
TAB.latitude,
TAB.type_etablissement,
TAB.adress,
TAB.no_uai,
TAB.zipcode,
TAB.city,
TAB.academie,
TAB.Annee_scolaire
