select 
TAB.annee,
SUM(CASE TAB.niveau WHEN 'Maternelle PS' THEN +1 ELSE +0 END) as "Maternelle PS",
SUM(CASE TAB.niveau WHEN 'Maternelle MS' THEN +1 ELSE +0 END) as "Maternelle MS",
SUM(CASE TAB.niveau WHEN 'Maternelle GS' THEN +1 ELSE +0 END) as "Maternelle GS",
SUM(CASE TAB.niveau WHEN 'CP' THEN +1 ELSE +0 END) as "CP",
SUM(CASE TAB.niveau WHEN 'CE1' THEN +1 ELSE +0 END) as "CE1",
SUM(CASE TAB.niveau WHEN 'CE2' THEN +1 ELSE +0 END) as "CE2",
SUM(CASE TAB.niveau WHEN 'CM1' THEN +1 ELSE +0 END) as "CM1",
SUM(CASE TAB.niveau WHEN 'CM2' THEN +1 ELSE +0 END) as "CM2",
SUM(CASE TAB.niveau WHEN '6e' THEN +1 ELSE +0 END) as "6e",
SUM(CASE TAB.niveau WHEN '5e' THEN +1 ELSE +0 END) as "5e",
SUM(CASE TAB.niveau WHEN '4e' THEN +1 ELSE +0 END) as "4e",
SUM(CASE TAB.niveau WHEN '3e' THEN +1 ELSE +0 END) as "3e",
SUM(CASE TAB.niveau WHEN '2nde' THEN +1 ELSE +0 END) as "2nde",
SUM(CASE TAB.niveau WHEN '1ere' THEN +1 ELSE +0 END) as "1ere",
SUM(CASE TAB.niveau WHEN 'Terminale' THEN +1 ELSE +0 END) as "Terminale",
SUM(CASE TAB.niveau WHEN 'Atelier scientifique' THEN +1 ELSE +0 END) as "Atelier scientifique",
SUM(CASE TAB.niveau WHEN 'Club scientifique' THEN +1 ELSE +0 END) as "Club scientifique",
SUM(CASE TAB.niveau WHEN 'Autre' THEN +1 
					when '' then +1 ELSE +0 END) as "Autre"

from (SELECT DISTINCT
observations.observationpk,
observations.protocolefk,
groupes.niveau,
--groupes.groupepk,
groupes.anneescol AS annee,
dico_protocoles.nom_courant AS protocole
  
FROM observations
LEFT JOIN observateurs ON observations.observateurfk = observateurs.observateurpk
LEFT JOIN groupes ON groupes.groupepk = observateurs.groupefk
left join dico_protocoles on dico_protocoles.protocolepk=observations.protocolefk

--where groupes.anneescol = '2021'
-- where observations.protocolefk = '6'
-- 1: Oiseaux, 2: Escargots, 3: Spipoll, 4:Vdt, 5:Sauvages, 6:Biolit
) TAB

group by TAB.annee
order by TAB.annee
