SELECT s.name as structure,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.espaceVert.nom')) as nom_espace_vert,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.transect.nom')) as nom_transect,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.date')) as `date`,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.heureDebut')) as debut,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.heureFin')) as fin,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.temperature')) as temp,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.vent')) as vent,
JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.couvertureNuageuse')) as couverture_nuageuse,
sp.* 
FROM espaces_verts.participations p
inner join observation_areas oa on p.observation_area_id =oa.id
inner join structures s on oa.structure_id =s.id
inner join (SELECT o.user_id ,o.participation_id,
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'robert-le-diable', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'robert-le-diable',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'myrtil', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'myrtil',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'procris', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'procris',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'pierides-blanches', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'pierides-blanches',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'amaryllis', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'amaryllis',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'hesperies-orangees', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'hesperies-orangees',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'grand-nacre-et-tabac-d-espagne', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'grand-nacre-et-tabac-d-espagne',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'cuivres', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'cuivres',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'megeres-et-nemusiens', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'megeres-et-nemusiens',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'fluores-et-colias-jaunes', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'fluores-et-colias-jaunes',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'fadets', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'fadets',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'azure-des-nerpruns', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'azure-des-nerpruns',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'papillon-indetermine', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'papillon-indetermine',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'tircis', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'tircis',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'vulcain', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'vulcain',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'souci', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'souci',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'hesperies-tachetees', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'hesperies-tachetees',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'paon-du-jour', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'paon-du-jour',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'azure-porte-queue', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'azure-porte-queue',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'lycenes-bleus', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'lycenes-bleus',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'demi-deuil-et-echiquiers', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'demi-deuil-et-echiquiers',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'carte-geographique', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'carte-geographique',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'brun-des-pelargoniums', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'brun-des-pelargoniums',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'gaze', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'gaze',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'tristan', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'tristan',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'marbres', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'marbres',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'machaons', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'machaons',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'belle-dame', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'belle-dame',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'petites-tortues', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'petites-tortues',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'argus-vert', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'argus-vert',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'citrons', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'citrons',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'melitees-et-damiers', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'melitees-et-damiers',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'silene', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'silene',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'moires', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'moires',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'grande-tortue', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'grande-tortue',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'flambes', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'flambes',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'grand-nacre-et-tabac-d''espagne', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'grand-nacre-et-tabac-despagne',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'mars-changeants', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'mars-changeants',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'petits-nacres', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'petits-nacres',
SUM(IF(right(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')), LENGTH(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')))-14) = 'sylvains', JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.abondance')), 0)) AS 'sylvains'
FROM espaces_verts.observations o
where left(JSON_UNQUOTE(JSON_EXTRACT(o.data,'$.taxon')),7)='propage'
group by o.user_id ,o.participation_id ) as sp on p.id =sp.participation_id
where s.name='Mairie de Grenoble' AND JSON_UNQUOTE(JSON_EXTRACT(p.data,'$.date')) LIKE "%2020%";

