1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| SELECT
m.idmembre,
m.prenom as 'prénom',
m.nom,
m.connu,
COUNT(x1.idparticipation) as 'nombre pointage',
COUNT(i1.idinscription) as 'nombre pointage',
SUM( IF( joursemaine = 2, 1, 0 ) ) as 'Lundi',
SUM( IF( joursemaine = 3, 1, 0 ) ) as 'Mardi',
SUM( IF( joursemaine = 4, 1, 0 ) ) as 'Mercredi',
SUM( IF( joursemaine = 5, 1, 0 ) ) as 'Jeudi',
SUM( IF( joursemaine = 6, 1, 0 ) ) as 'Vendredi',
SUM( IF( joursemaine = 7, 1, 0 ) ) as 'Samedi',
SUM( IF( joursemaine = 1, 1, 0 ) ) as 'Dimanche'
FROM gestion_membre AS m
LEFT JOIN gestion_participation as x1 ON x1.idmembre = m.idmembre
INNER JOIN
(
SELECT
s2.idsession,
s2.date_session,
DAYOFWEEK(s2.date_session) as joursemaine,
s2.idplanning
FROM gestion_session as s2
WHERE "2010-08-01 00:00:00" <= s2.date_session AND s2.date_session <= "2011-08-01 00:00:00"
) as s1 ON x1.idsession = s1.idsession
LEFT JOIN
(
SELECT
i2.idinscription,
i2.idmembre,
i2.idplanning,
i2.carte
FROM gestion_inscription as i2
WHERE "2010-08-01 00:00:00" <= i2.date_debut AND i2.date_debut <= "2011-08-01 00:00:00" )
) as i1 ON i1.idmembre = m.idmembre
GROUP BY m.idmembre |
Partager