Requète SQL avec condition where

Résolu
LaChaux78 -  
Bonjour,
J'ai une requète qui me retourne le nombre de répétition par an. 
	// Nombre de répétitions par année
	$sql = "SELECT YEAR(cdc_repetitions.drepet) AS annee, COUNT(cdc_repetitions.idrep) AS nbrep
			FROM cdc_repetitions
			GROUP BY annee
			ORDER BY annee";
	$result = executeQuery($sql,$datas);

	echo '<div class="nb_repetition"><h1>Nombre de répétitions par année</h1>';
	echo '<table border="0" cellpadding="0" cellspacing="0">';
	echo '<tr><td class="c1">Année</td><td class="c2">Nombre</td></tr>';

	while ($donnees = $result->fetch()) {
		echo '<tr><td class="c3">'.$donnees->annee.'</td><td class="c4">'.$donnees->nbrep.'</td></tr>';
	}
	echo '</table>';
	echo '</div>';

Mais je souhaite pouvoir afficher uniquement le nombre de répétitions par an si la valeur d'une colonne est à 2.
J'ai une table cdc_assister avec un champ idloc soit 1 soit 2
J'ai fait cela mais le résultat n'est pas bon.
Merci pour votre aide. 
	$sql = "SELECT YEAR(cdc_repetitions.drepet) AS annee, COUNT(cdc_repetitions.idrep) AS nbrep
			FROM cdc_repetitions,cdc_assister,cdc_locaux
			WHERE cdc_locaux.idloc = cdc_assister.idloc
			AND cdc_repetitions.idrep = cdc_assister.idrep
			AND cdc_assister.idloc = 2
			GROUP BY annee
			ORDER BY annee";
	$result = executeQuery($sql,$datas);

	echo '<div class="nb_repetition"><h1>Nombre de répétitions par année</h1>';
	echo '<table border="0" cellpadding="0" cellspacing="0">';
	echo '<tr><td class="c1">Année</td><td class="c2">Nombre</td></tr>';

	while ($donnees = $result->fetch()) {
		echo '<tr><td class="c3">'.$donnees->annee.'</td><td class="c4">'.$donnees->nbrep.'</td></tr>';
	}
	echo '</table>';
	echo '</div>';

13 réponses

  1.  jordane45 Modérateur
    Bonjour,

    Que donne cette requête : 
    SELECT YEAR(R.drepet) AS annee, 
       COUNT(R.idrep) AS nbrep
FROM cdc_repetitions R
LEFT JOIN cdc_assister A ON A.idrep = R.idrep AND A.idloc = 2
LEFT JOIN cdc_locaux L ON L.idloc = A.idloc
GROUP BY annee
ORDER BY annee


    NB: Pense à tester tes requêtes directement dans ta BDD via phpmyadmin plutôt que directement dans ton code....

    1. LaChaux78 Auteur
      Bonjour,
      Ca me retourne cette erreur. 
      Erreur ! SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cdc_repetitions.drepet' in 'field list'
      1. jordane45 Modérateur
        Change le début de la requête 
        SELECT YEAR(R.drepet) AS annee, 
       COUNT(R.idrep) AS nbrep

        Sachant que je me suis servi de ta requête à toi...
        Si ça ne fonctionne toujours pas après ça, donne nous un dump de tes tables (structure + données )
    2. LaChaux78 Auteur
      Par contre cela me retourne un résultat qui n'a rien à voir avec la réalité.
      Voici la structure de mes tables.
      Merci 
      mysql> show full columns from cdc_repetitions;
+--------+---------+-----------+------+-----+---------+----------------+---------------------------------+-----------------------+
| Field  | Type    | Collation | Null | Key | Default | Extra          | Privileges                      | Comment               |
+--------+---------+-----------+------+-----+---------+----------------+---------------------------------+-----------------------+
| idrep  | int(11) | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references | ID de la répétition   |
| drepet | date    | NULL      | NO   |     | NULL    |                | select,insert,update,references | Date de la répétition |
+--------+---------+-----------+------+-----+---------+----------------+---------------------------------+-----------------------+
2 rows in set (0.01 sec)
mysql> show full columns from cdc_locaux;
+---------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------------------------+
| Field   | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment                   |
+---------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------------------------+
| idloc   | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | ID du local de répétition |
| local   | varchar(40) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | Désignation du local      |
| cpostal | char(5)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | Code postal du local      |
| ville   | varchar(25) | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | Ville du local            |
+---------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------------------------+
4 rows in set (0.02 sec)
mysql> show full columns from cdc_assister;
+--------+---------+-----------+------+-----+---------+-------+---------------------------------+---------------------------+
| Field  | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment                   |
+--------+---------+-----------+------+-----+---------+-------+---------------------------------+---------------------------+
| idpers | int(11) | NULL      | NO   | PRI | NULL    |       | select,insert,update,references | ID du répétant            |
| idrep  | int(11) | NULL      | NO   | PRI | NULL    |       | select,insert,update,references | ID de la répétition       |
| idloc  | int(11) | NULL      | NO   | PRI | NULL    |       | select,insert,update,references | ID du local de répétition |
+--------+---------+-----------+------+-----+---------+-------+---------------------------------+---------------------------+
3 rows in set (0.01 sec)
      1. jordane45 Modérateur
        Je t'ai demandé un DUMP (structure + données ) 
         mysqldump -u user -p maBase > maBase_backup.sql

        Ensuite tu colles le contenu du fichier sql sur le forum entre balises de code sql.
    3. LaChaux78 Auteur
      Bonjour
      Comme j'ouvre une console Mysql pour la commande s'il te plait.
      Merci
      1. jordane45 Modérateur
        Partons de ton message précédent...
        Tu as posté : 
        mysql> show full columns from cdc_repetitions;

        Tu as donc :
        1 - Ouvert une console
        2 - ouvert la connexion à la bdd
        3 - Tapé la ligne de code 
         show full columns from cdc_repetitions;


        Pour faire le DUMP ...
        Tu reprends l'étape 1 : Ouvrir la console
        Puis tu tapes directement la ligne de code que je t'ai donné (en remplaçant les variables par TES valeurs )
    5. LaChaux78 Auteur
      en fait c'est la console de wampserver que je me suis servi et utiliser en local donc...
      1. jordane45 Modérateur
        tu ouvres une invite de commande msdos.... une "cmd" ... une console quoi....
      2. jordane45 Modérateur
        Ou sinon tu passes par ton phpmyadmin et tu génères un export .... c'est pareil.
    6. LaChaux78 Auteur 
      --
-- Structure de la table `cdc_assister`
--

CREATE TABLE `cdc_assister` (
  `idpers` int(11) NOT NULL COMMENT 'ID du répétant',
  `idrep` int(11) NOT NULL COMMENT 'ID de la répétition',
  `idloc` int(11) NOT NULL COMMENT 'ID du local de répétition'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table de relation : adhérents - assister - répétitions';

--
-- Déchargement des données de la table `cdc_assister`
--

INSERT INTO `cdc_assister` (`idpers`, `idrep`, `idloc`) VALUES
(1, 1, 1),
(1, 2, 1),
(1, 3, 1),
(1, 4, 1),
(1, 5, 1),
(1, 6, 1),
(1, 7, 1),
(1, 9, 1),
(1, 11, 1),
(1, 13, 1),
(1, 14, 1),
(1, 15, 1),
(1, 16, 1),
(1, 18, 1),
(1, 19, 1),
(1, 21, 1),
(1, 24, 1),
(1, 25, 1),
(1, 26, 1),
(1, 27, 1),
(1, 28, 1),
(1, 30, 1),
(1, 31, 1),
(1, 32, 1),
(1, 33, 1),
(1, 34, 1),
(1, 35, 1),
(1, 36, 1),
(1, 38, 1),
(1, 39, 1),
(1, 40, 1),
(1, 41, 1),
(1, 42, 1),
(1, 43, 1),
(1, 44, 1),
(1, 46, 1),
(1, 47, 1),
(1, 48, 1),
(1, 49, 1),
(1, 50, 1),
(1, 52, 1),
(1, 53, 1),
(1, 54, 1),
(1, 55, 1),
(1, 56, 1),
(1, 57, 1),
(1, 58, 1),
(1, 59, 1),
(1, 60, 1),
(1, 66, 1),
(1, 67, 1),
(1, 68, 1),
(1, 71, 1),
(1, 72, 1),
(1, 73, 1),
(1, 77, 1),
(1, 78, 1),
(1, 79, 1),
(1, 80, 1),
(1, 81, 1),
(1, 82, 1),
(1, 83, 1),
(1, 85, 1),
(1, 86, 1),
(1, 87, 1),
(1, 88, 2),
(1, 90, 1),
(1, 93, 1),
(1, 94, 1),
(1, 95, 2),
(1, 97, 1),
(1, 100, 1),
(1, 101, 1),
(1, 102, 1),
(1, 103, 1),
(1, 105, 1),
(1, 106, 2),
(1, 107, 2),
(1, 108, 1),
(1, 109, 1),
(1, 110, 1),
(1, 111, 1),
(1, 112, 1),
(1, 113, 1),
(1, 114, 1),
(1, 118, 1),
(1, 119, 1),
(1, 120, 1),
(1, 121, 1),
(1, 122, 1),
(1, 124, 1),
(1, 125, 1),
(1, 126, 1),
(1, 127, 1),
(1, 128, 1),
(1, 130, 1),
(1, 131, 1),
(1, 133, 1),
(1, 134, 1),
(1, 135, 1),
(1, 136, 1),
(1, 138, 2),
(1, 139, 1),
(1, 140, 1),
(1, 141, 1),
(2, 1, 1),
(2, 2, 1),
(2, 3, 1),
(2, 4, 1),
(2, 5, 1),
(2, 6, 1),
(2, 7, 1),
(2, 8, 1),
(2, 9, 1),
(2, 10, 1),
(2, 11, 1),
(2, 12, 1),
(2, 13, 1),
(2, 14, 1),
(2, 15, 1),
(2, 16, 1),
(2, 17, 1),
(2, 18, 1),
(2, 19, 1),
(2, 20, 1),
(2, 21, 1),
(2, 22, 1),
(2, 23, 1),
(2, 24, 1),
(2, 25, 1),
(2, 26, 1),
(2, 28, 1),
(2, 29, 1),
(2, 30, 1),
(2, 31, 1),
(2, 32, 1),
(2, 33, 1),
(2, 34, 1),
(2, 35, 1),
(2, 37, 1),
(2, 38, 1),
(2, 39, 1),
(2, 40, 1),
(2, 41, 1),
(2, 42, 1),
(2, 43, 1),
(2, 44, 1),
(2, 45, 1),
(2, 46, 1),
(2, 47, 1),
(2, 48, 1),
(2, 49, 1),
(2, 50, 1),
(2, 51, 1),
(2, 52, 1),
(2, 53, 1),
(2, 54, 1),
(2, 55, 1),
(2, 56, 1),
(2, 57, 1),
(2, 59, 1),
(2, 60, 1),
(2, 61, 1),
(2, 62, 1),
(2, 63, 1),
(2, 64, 1),
(2, 65, 1),
(2, 66, 1),
(2, 67, 1),
(2, 68, 1),
(2, 69, 1),
(2, 70, 1),
(2, 71, 1),
(2, 72, 1),
(2, 73, 1),
(2, 74, 1),
(2, 76, 1),
(2, 77, 1),
(2, 78, 1),
(2, 79, 1),
(2, 80, 1),
(2, 81, 1),
(2, 83, 1),
(2, 84, 2),
(2, 85, 1),
(2, 86, 1),
(2, 87, 1),
(2, 88, 2),
(2, 89, 1),
(2, 90, 1),
(2, 91, 2),
(2, 92, 1),
(2, 93, 1),
(2, 96, 1),
(2, 97, 1),
(2, 98, 1),
(2, 99, 2),
(2, 100, 1),
(2, 101, 1),
(2, 102, 1),
(2, 103, 1),
(2, 104, 2),
(2, 106, 2),
(2, 107, 2),
(2, 108, 1),
(2, 109, 1),
(2, 113, 1),
(2, 114, 1),
(2, 115, 1),
(2, 116, 1),
(2, 117, 1),
(2, 118, 1),
(2, 119, 1),
(2, 120, 1),
(2, 121, 1),
(2, 122, 1),
(2, 123, 1),
(2, 124, 1),
(2, 125, 1),
(2, 126, 1),
(2, 127, 1),
(2, 128, 1),
(2, 129, 1),
(2, 130, 1),
(2, 131, 1),
(2, 132, 1),
(2, 133, 1),
(2, 134, 1),
(2, 135, 1),
(2, 136, 1),
(2, 137, 2),
(2, 138, 2),
(2, 139, 1),
(2, 140, 1),
(2, 141, 1),
(3, 3, 1),
(3, 8, 1),
(3, 14, 1),
(4, 20, 1),
(4, 21, 1),
(4, 53, 1),
(4, 62, 1),
(4, 106, 2),
(4, 107, 2),
(13, 1, 1),
(13, 2, 1),
(13, 3, 1),
(13, 4, 1),
(13, 5, 1),
(13, 6, 1),
(13, 7, 1),
(13, 8, 1),
(13, 9, 1),
(13, 10, 1),
(13, 11, 1),
(13, 12, 1),
(13, 13, 1),
(13, 14, 1),
(13, 15, 1),
(13, 16, 1),
(13, 17, 1),
(13, 19, 1),
(13, 20, 1),
(13, 21, 1),
(13, 23, 1),
(13, 24, 1),
(13, 25, 1),
(13, 26, 1),
(13, 27, 1),
(13, 28, 1),
(13, 29, 1),
(13, 30, 1),
(13, 32, 1),
(13, 33, 1),
(13, 34, 1),
(13, 35, 1),
(13, 36, 1),
(13, 37, 1),
(13, 38, 1),
(13, 40, 1),
(13, 41, 1),
(13, 42, 1),
(13, 43, 1),
(13, 44, 1),
(13, 45, 1),
(13, 46, 1),
(13, 48, 1),
(13, 51, 1),
(13, 52, 1),
(13, 53, 1),
(13, 58, 1),
(13, 60, 1),
(13, 61, 1),
(13, 62, 1),
(13, 65, 1),
(13, 66, 1),
(13, 67, 1),
(13, 69, 1),
(13, 71, 1),
(13, 74, 1),
(13, 76, 1),
(13, 77, 1),
(13, 78, 1),
(13, 81, 1),
(13, 82, 1),
(13, 83, 1),
(13, 84, 2),
(13, 85, 1),
(13, 86, 1),
(13, 87, 1),
(13, 88, 2),
(13, 89, 1),
(13, 91, 2),
(13, 92, 1),
(13, 93, 1),
(13, 95, 2),
(13, 96, 1),
(13, 97, 1),
(13, 98, 1),
(13, 99, 2),
(13, 100, 1),
(13, 101, 1),
(13, 103, 1),
(13, 104, 2),
(13, 108, 1),
(13, 109, 1),
(13, 110, 1),
(13, 111, 1),
(13, 112, 1),
(13, 113, 1),
(13, 114, 1),
(13, 115, 1),
(13, 117, 1),
(13, 118, 1),
(13, 119, 1),
(13, 121, 1),
(13, 122, 1),
(13, 123, 1),
(13, 126, 1),
(13, 127, 1),
(13, 128, 1),
(13, 129, 1),
(13, 130, 1),
(13, 131, 1),
(13, 132, 1),
(13, 133, 1),
(13, 134, 1),
(13, 135, 1),
(13, 136, 1),
(13, 139, 1),
(13, 140, 1),
(21, 5, 1),
(21, 8, 1),
(21, 10, 1),
(21, 18, 1),
(21, 25, 1),
(21, 26, 1),
(21, 30, 1),
(21, 41, 1),
(21, 44, 1),
(21, 47, 1),
(21, 72, 1),
(21, 77, 1),
(21, 79, 1),
(21, 100, 1),
(21, 107, 2),
(21, 117, 1),
(21, 120, 1),
(22, 3, 1),
(22, 5, 1),
(22, 6, 1),
(22, 7, 1),
(22, 11, 1),
(22, 12, 1),
(22, 15, 1),
(22, 25, 1),
(22, 26, 1),
(22, 28, 1),
(22, 30, 1),
(22, 32, 1),
(22, 34, 1),
(22, 35, 1),
(22, 40, 1),
(22, 41, 1),
(22, 42, 1),
(22, 43, 1),
(22, 45, 1),
(22, 47, 1),
(22, 49, 1),
(22, 50, 1),
(22, 51, 1),
(22, 52, 1),
(22, 53, 1),
(22, 54, 1),
(22, 56, 1),
(22, 57, 1),
(22, 58, 1),
(22, 59, 1),
(22, 63, 1),
(22, 64, 1),
(22, 66, 1),
(22, 67, 1),
(22, 72, 1),
(22, 73, 1),
(22, 74, 1),
(22, 76, 1),
(22, 77, 1),
(22, 78, 1),
(22, 80, 1),
(22, 81, 1),
(22, 82, 1),
(22, 86, 1),
(22, 87, 1),
(22, 91, 2),
(22, 92, 1),
(22, 97, 1),
(22, 98, 1),
(22, 100, 1),
(22, 102, 1),
(22, 105, 1),
(22, 109, 1),
(22, 112, 1),
(22, 113, 1),
(22, 114, 1),
(22, 115, 1),
(22, 116, 1),
(22, 117, 1),
(22, 118, 1),
(22, 119, 1),
(22, 120, 1),
(22, 121, 1),
(22, 124, 1),
(22, 126, 1),
(22, 127, 1),
(22, 128, 1),
(22, 129, 1),
(22, 130, 1),
(22, 131, 1),
(22, 132, 1),
(22, 133, 1),
(22, 134, 1),
(22, 135, 1),
(22, 136, 1),
(22, 139, 1),
(22, 141, 1),
(27, 4, 1),
(27, 10, 1),
(27, 11, 1),
(27, 12, 1),
(27, 18, 1),
(27, 25, 1),
(27, 26, 1),
(27, 27, 1),
(27, 28, 1),
(27, 29, 1),
(27, 34, 1),
(27, 35, 1),
(27, 39, 1),
(27, 40, 1),
(27, 43, 1),
(27, 45, 1),
(27, 49, 1),
(27, 54, 1),
(27, 61, 1),
(27, 68, 1),
(27, 71, 1),
(27, 72, 1),
(27, 76, 1),
(27, 77, 1),
(27, 80, 1),
(27, 81, 1),
(27, 85, 1),
(27, 88, 2),
(27, 89, 1),
(27, 100, 1),
(27, 101, 1),
(27, 103, 1),
(27, 110, 1),
(27, 111, 1),
(27, 112, 1),
(27, 117, 1),
(27, 118, 1),
(27, 120, 1),
(27, 122, 1),
(27, 124, 1),
(27, 125, 1),
(27, 126, 1),
(27, 127, 1),
(27, 128, 1),
(27, 134, 1),
(27, 139, 1),
(28, 3, 1),
(28, 4, 1),
(28, 5, 1),
(28, 6, 1),
(28, 8, 1),
(28, 9, 1),
(28, 10, 1),
(28, 11, 1),
(28, 12, 1),
(28, 13, 1),
(28, 14, 1),
(28, 15, 1),
(28, 16, 1),
(28, 18, 1),
(28, 19, 1),
(28, 21, 1),
(28, 22, 1),
(28, 23, 1),
(28, 24, 1),
(28, 25, 1),
(28, 26, 1),
(28, 27, 1),
(28, 28, 1),
(28, 29, 1),
(28, 30, 1),
(28, 31, 1),
(28, 32, 1),
(28, 33, 1),
(28, 34, 1),
(28, 35, 1),
(28, 37, 1),
(28, 38, 1),
(28, 39, 1),
(28, 40, 1),
(28, 41, 1),
(28, 42, 1),
(28, 43, 1),
(28, 44, 1),
(28, 45, 1),
(28, 46, 1),
(28, 47, 1),
(28, 48, 1),
(28, 49, 1),
(28, 50, 1),
(28, 51, 1),
(28, 53, 1),
(28, 54, 1),
(28, 55, 1),
(28, 56, 1),
(28, 57, 1),
(28, 59, 1),
(28, 60, 1),
(28, 62, 1),
(28, 64, 1),
(28, 65, 1),
(28, 66, 1),
(28, 67, 1),
(28, 69, 1),
(28, 70, 1),
(28, 71, 1),
(28, 72, 1),
(28, 73, 1),
(28, 74, 1),
(28, 75, 1),
(28, 76, 1),
(28, 77, 1),
(28, 78, 1),
(28, 79, 1),
(28, 80, 1),
(28, 81, 1),
(28, 82, 1),
(28, 83, 1),
(28, 84, 2),
(28, 85, 1),
(28, 86, 1),
(28, 87, 1),
(28, 88, 2),
(28, 89, 1),
(28, 90, 1),
(28, 91, 2),
(28, 92, 1),
(28, 93, 1),
(28, 94, 1),
(28, 95, 2),
(28, 96, 1),
(28, 98, 1),
(28, 99, 2),
(28, 100, 1),
(28, 102, 1),
(28, 103, 1),
(28, 105, 1),
(28, 106, 2),
(28, 108, 1),
(28, 109, 1),
(28, 110, 1),
(28, 111, 1),
(28, 112, 1),
(28, 113, 1),
(28, 114, 1),
(28, 115, 1),
(28, 116, 1),
(28, 117, 1),
(28, 118, 1),
(28, 119, 1),
(28, 120, 1),
(28, 121, 1),
(28, 122, 1),
(28, 124, 1),
(28, 126, 1),
(28, 128, 1),
(28, 129, 1),
(28, 130, 1),
(28, 131, 1),
(28, 132, 1),
(28, 133, 1),
(28, 134, 1),
(28, 136, 1),
(28, 139, 1),
(28, 140, 1),
(28, 141, 1),
(30, 1, 1),
(30, 2, 1),
(30, 3, 1),
(30, 4, 1),
(30, 5, 1),
(30, 6, 1),
(30, 7, 1),
(30, 8, 1),
(30, 9, 1),
(30, 10, 1),
(30, 11, 1),
(30, 12, 1),
(30, 13, 1),
(30, 14, 1),
(30, 15, 1),
(30, 16, 1),
(30, 17, 1),
(30, 18, 1),
(30, 19, 1),
(30, 21, 1),
(30, 22, 1),
(30, 23, 1),
(30, 24, 1),
(30, 25, 1),
(30, 26, 1),
(30, 27, 1),
(30, 28, 1),
(30, 30, 1),
(30, 31, 1),
(30, 32, 1),
(30, 33, 1),
(30, 34, 1),
(30, 35, 1),
(30, 37, 1),
(30, 38, 1),
(30, 39, 1),
(30, 40, 1),
(30, 41, 1),
(30, 42, 1),
(30, 43, 1),
(30, 45, 1),
(30, 46, 1),
(30, 47, 1),
(30, 48, 1),
(30, 49, 1),
(30, 50, 1),
(30, 53, 1),
(30, 54, 1),
(30, 55, 1),
(30, 56, 1),
(30, 57, 1),
(30, 59, 1),
(30, 60, 1),
(30, 62, 1),
(30, 64, 1),
(30, 65, 1),
(30, 66, 1),
(30, 67, 1),
(30, 68, 1),
(30, 69, 1),
(30, 70, 1),
(30, 71, 1),
(30, 72, 1),
(30, 73, 1),
(30, 74, 1),
(30, 75, 1),
(30, 76, 1),
(30, 77, 1),
(30, 78, 1),
(30, 79, 1),
(30, 80, 1),
(30, 81, 1),
(30, 82, 1),
(30, 83, 1),
(30, 84, 2),
(30, 86, 1),
(30, 87, 1),
(30, 89, 1),
(30, 90, 1),
(30, 91, 2),
(30, 92, 1),
(30, 93, 1),
(30, 94, 1),
(30, 95, 2),
(30, 96, 1),
(30, 98, 1),
(30, 100, 1),
(30, 102, 1),
(30, 103, 1),
(30, 105, 1),
(30, 106, 2),
(30, 108, 1),
(30, 110, 1),
(30, 111, 1),
(30, 112, 1),
(30, 113, 1),
(30, 114, 1),
(30, 115, 1),
(30, 116, 1),
(30, 117, 1),
(30, 118, 1),
(30, 119, 1),
(30, 120, 1),
(30, 121, 1),
(30, 122, 1),
(30, 124, 1),
(30, 126, 1),
(30, 128, 1),
(30, 129, 1),
(30, 130, 1),
(30, 131, 1),
(30, 132, 1),
(30, 133, 1),
(30, 134, 1),
(30, 136, 1),
(30, 139, 1),
(30, 141, 1),
(57, 87, 1),
(57, 94, 1),
(57, 97, 1),
(57, 98, 1),
(57, 100, 1),
(57, 109, 1),
(57, 115, 1),
(57, 116, 1),
(57, 117, 1),
(57, 119, 1),
(57, 120, 1),
(57, 125, 1),
(80, 1, 1),
(80, 5, 1),
(80, 7, 1),
(80, 8, 1),
(80, 10, 1),
(80, 11, 1),
(80, 14, 1),
(80, 15, 1),
(80, 16, 1),
(80, 17, 1),
(80, 18, 1),
(80, 19, 1),
(80, 24, 1),
(80, 25, 1),
(80, 26, 1),
(80, 27, 1),
(80, 28, 1),
(80, 29, 1),
(80, 32, 1),
(80, 35, 1),
(80, 36, 1),
(80, 40, 1),
(80, 41, 1),
(80, 43, 1),
(80, 45, 1),
(80, 47, 1),
(80, 49, 1),
(80, 50, 1),
(80, 51, 1),
(80, 53, 1),
(80, 55, 1),
(80, 56, 1),
(80, 57, 1),
(80, 58, 1),
(80, 61, 1),
(80, 66, 1),
(80, 73, 1),
(80, 111, 1),
(91, 1, 1),
(91, 5, 1),
(91, 7, 1),
(91, 8, 1),
(91, 10, 1),
(91, 11, 1),
(91, 14, 1),
(91, 15, 1),
(91, 16, 1),
(91, 17, 1),
(91, 18, 1),
(91, 19, 1),
(91, 24, 1),
(91, 28, 1),
(91, 29, 1),
(91, 33, 1),
(91, 35, 1),
(91, 36, 1),
(91, 40, 1),
(91, 41, 1),
(91, 43, 1),
(91, 45, 1),
(91, 50, 1),
(91, 51, 1),
(91, 52, 1),
(91, 53, 1),
(91, 55, 1),
(91, 56, 1),
(91, 57, 1),
(91, 58, 1),
(91, 60, 1),
(91, 61, 1),
(91, 66, 1),
(91, 67, 1),
(91, 68, 1),
(91, 69, 1),
(91, 70, 1),
(91, 72, 1),
(91, 73, 1),
(91, 75, 1),
(91, 76, 1),
(91, 77, 1),
(91, 78, 1),
(91, 81, 1),
(91, 83, 1),
(91, 86, 1),
(91, 89, 1),
(91, 90, 1),
(91, 91, 2),
(91, 95, 2),
(91, 96, 1),
(91, 99, 2),
(91, 100, 1),
(91, 106, 2),
(91, 107, 2),
(91, 111, 1),
(91, 112, 1),
(100, 2, 1),
(100, 5, 1),
(100, 6, 1),
(100, 7, 1),
(100, 8, 1),
(100, 9, 1),
(100, 10, 1),
(100, 11, 1),
(100, 12, 1),
(100, 14, 1),
(100, 18, 1),
(100, 19, 1),
(100, 26, 1),
(100, 29, 1),
(100, 32, 1),
(100, 33, 1),
(100, 35, 1),
(100, 36, 1),
(100, 45, 1),
(100, 49, 1),
(100, 51, 1),
(100, 52, 1),
(100, 53, 1),
(100, 54, 1),
(100, 55, 1),
(100, 56, 1),
(100, 57, 1),
(100, 58, 1),
(100, 59, 1),
(100, 60, 1),
(100, 68, 1),
(100, 70, 1),
(100, 72, 1),
(100, 74, 1),
(100, 76, 1),
(100, 77, 1),
(100, 100, 1),
(100, 102, 1),
(100, 103, 1),
(100, 109, 1),
(100, 111, 1),
(100, 115, 1),
(100, 118, 1),
(100, 119, 1),
(100, 120, 1),
(100, 122, 1),
(100, 124, 1),
(100, 126, 1),
(100, 127, 1),
(100, 132, 1),
(100, 133, 1),
(100, 134, 1),
(100, 135, 1),
(100, 139, 1),
(100, 140, 1),
(106, 2, 1),
(106, 3, 1),
(106, 4, 1),
(106, 5, 1),
(106, 6, 1),
(106, 7, 1),
(106, 8, 1),
(106, 9, 1),
(106, 10, 1),
(106, 12, 1),
(106, 16, 1),
(106, 17, 1),
(106, 23, 1),
(106, 26, 1),
(106, 27, 1),
(106, 30, 1),
(106, 34, 1),
(106, 36, 1),
(106, 37, 1),
(106, 39, 1),
(106, 40, 1),
(106, 41, 1),
(106, 42, 1),
(106, 43, 1),
(106, 45, 1),
(106, 46, 1),
(106, 47, 1),
(106, 48, 1),
(106, 49, 1),
(106, 50, 1),
(106, 53, 1),
(106, 57, 1),
(106, 59, 1),
(106, 60, 1),
(106, 66, 1),
(106, 67, 1),
(106, 68, 1),
(106, 69, 1),
(106, 70, 1),
(106, 71, 1),
(106, 72, 1),
(106, 76, 1),
(106, 77, 1),
(106, 78, 1),
(106, 81, 1),
(106, 83, 1),
(106, 85, 1),
(106, 87, 1),
(106, 89, 1),
(106, 98, 1),
(106, 100, 1),
(106, 101, 1),
(106, 103, 1),
(106, 105, 1),
(106, 109, 1),
(106, 110, 1),
(106, 111, 1),
(106, 112, 1),
(106, 115, 1),
(106, 118, 1),
(106, 120, 1),
(106, 122, 1),
(106, 124, 1),
(106, 125, 1),
(106, 126, 1),
(106, 127, 1),
(106, 128, 1),
(106, 129, 1),
(106, 130, 1),
(106, 131, 1),
(106, 132, 1),
(106, 133, 1),
(106, 134, 1),
(106, 135, 1),
(106, 139, 1),
(106, 140, 1),
(106, 141, 1),
(110, 1, 1),
(110, 2, 1),
(110, 3, 1),
(110, 4, 1),
(110, 5, 1),
(110, 6, 1),
(110, 8, 1),
(110, 9, 1),
(110, 10, 1),
(110, 11, 1),
(110, 12, 1),
(110, 15, 1),
(110, 16, 1),
(110, 17, 1),
(110, 19, 1),
(110, 25, 1),
(110, 27, 1),
(110, 28, 1),
(110, 29, 1),
(110, 30, 1),
(110, 32, 1),
(110, 33, 1),
(110, 34, 1),
(110, 35, 1),
(110, 36, 1),
(110, 37, 1),
(110, 40, 1),
(110, 41, 1),
(110, 42, 1),
(110, 43, 1),
(110, 44, 1),
(110, 45, 1),
(110, 46, 1),
(110, 49, 1),
(110, 50, 1),
(110, 51, 1),
(110, 52, 1),
(110, 53, 1),
(110, 56, 1),
(110, 57, 1),
(110, 58, 1),
(110, 59, 1),
(110, 60, 1),
(110, 61, 1),
(110, 64, 1),
(110, 66, 1),
(110, 67, 1),
(110, 70, 1),
(110, 71, 1),
(110, 73, 1),
(110, 74, 1),
(110, 76, 1),
(110, 77, 1),
(110, 78, 1),
(110, 79, 1),
(110, 83, 1),
(110, 84, 2),
(110, 85, 1),
(110, 86, 1),
(110, 87, 1),
(110, 88, 2),
(110, 89, 1),
(110, 90, 1),
(110, 91, 2),
(110, 92, 1),
(110, 93, 1),
(110, 94, 1),
(110, 95, 2),
(110, 96, 1),
(110, 97, 1),
(110, 98, 1),
(110, 99, 2),
(110, 100, 1),
(110, 101, 1),
(110, 104, 2),
(110, 105, 1),
(110, 106, 2),
(110, 107, 2),
(110, 109, 1),
(110, 111, 1),
(110, 113, 1),
(110, 114, 1),
(110, 115, 1),
(110, 116, 1),
(110, 117, 1),
(110, 118, 1),
(110, 119, 1),
(110, 120, 1),
(110, 125, 1),
(110, 126, 1),
(110, 127, 1),
(110, 128, 1),
(110, 129, 1),
(110, 130, 1),
(110, 131, 1),
(110, 135, 1),
(110, 136, 1),
(110, 137, 2),
(110, 139, 1),
(110, 140, 1),
(111, 3, 1),
(111, 32, 1),
(111, 47, 1),
(111, 49, 1),
(111, 53, 1),
(113, 1, 1),
(113, 2, 1),
(113, 3, 1),
(113, 4, 1),
(113, 5, 1),
(113, 6, 1),
(113, 7, 1),
(113, 8, 1),
(113, 9, 1),
(113, 10, 1),
(113, 13, 1),
(113, 16, 1),
(113, 17, 1),
(113, 18, 1),
(113, 19, 1),
(113, 20, 1),
(113, 21, 1),
(113, 22, 1),
(113, 24, 1),
(113, 25, 1),
(113, 26, 1),
(113, 29, 1),
(113, 32, 1),
(113, 33, 1),
(113, 35, 1),
(113, 36, 1),
(113, 37, 1),
(113, 38, 1),
(113, 39, 1),
(113, 40, 1),
(113, 41, 1),
(113, 42, 1),
(113, 43, 1),
(113, 45, 1),
(113, 48, 1),
(113, 49, 1),
(113, 50, 1),
(113, 51, 1),
(113, 55, 1),
(113, 56, 1),
(113, 57, 1),
(113, 58, 1),
(113, 59, 1),
(113, 60, 1),
(113, 61, 1),
(113, 62, 1),
(113, 66, 1),
(113, 67, 1),
(113, 68, 1),
(113, 69, 1),
(113, 70, 1),
(113, 71, 1),
(113, 74, 1),
(113, 76, 1),
(113, 77, 1),
(113, 78, 1),
(113, 79, 1),
(113, 80, 1),
(113, 81, 1),
(113, 82, 1),
(113, 83, 1),
(113, 85, 1),
(113, 86, 1),
(113, 90, 1),
(113, 92, 1),
(113, 100, 1),
(113, 103, 1),
(113, 104, 2),
(113, 107, 2),
(113, 110, 1),
(117, 2, 1),
(117, 3, 1),
(117, 4, 1),
(117, 5, 1),
(117, 6, 1),
(117, 7, 1),
(117, 8, 1),
(117, 9, 1),
(117, 10, 1),
(117, 11, 1),
(117, 12, 1),
(117, 13, 1),
(117, 14, 1),
(117, 15, 1),
(117, 16, 1),
(117, 17, 1),
(117, 18, 1),
(117, 20, 1),
(117, 22, 1),
(117, 23, 1),
(117, 24, 1),
(117, 25, 1),
(117, 26, 1),
(117, 27, 1),
(117, 28, 1),
(117, 29, 1),
(117, 30, 1),
(117, 31, 1),
(117, 32, 1),
(117, 33, 1),
(117, 34, 1),
(117, 35, 1),
(117, 36, 1),
(117, 37, 1),
(117, 39, 1),
(117, 40, 1),
(117, 41, 1),
(117, 42, 1),
(117, 43, 1),
(117, 45, 1),
(117, 46, 1),
(117, 47, 1),
(117, 48, 1),
(117, 49, 1),
(117, 50, 1),
(117, 51, 1),
(117, 52, 1),
(117, 53, 1),
(117, 54, 1),
(117, 55, 1),
(117, 56, 1),
(117, 57, 1),
(117, 59, 1),
(117, 60, 1),
(117, 62, 1),
(117, 63, 1),
(117, 64, 1),
(117, 65, 1),
(117, 66, 1),
(117, 67, 1),
(117, 69, 1),
(117, 70, 1),
(117, 71, 1),
(117, 72, 1),
(117, 73, 1),
(117, 74, 1),
(117, 76, 1),
(117, 77, 1),
(117, 78, 1),
(117, 81, 1),
(117, 82, 1),
(117, 83, 1),
(117, 84, 2),
(117, 85, 1),
(117, 87, 1),
(117, 88, 2),
(117, 89, 1),
(117, 90, 1),
(117, 93, 1),
(117, 95, 2),
(117, 96, 1),
(117, 97, 1),
(117, 98, 1),
(117, 99, 2),
(117, 100, 1),
(117, 105, 1),
(117, 106, 2),
(117, 109, 1),
(117, 110, 1),
(117, 112, 1),
(117, 113, 1),
(117, 114, 1),
(117, 115, 1),
(117, 116, 1),
(117, 117, 1),
(117, 118, 1),
(117, 119, 1),
(117, 120, 1),
(117, 121, 1),
(117, 122, 1),
(117, 123, 1),
(117, 124, 1),
(117, 125, 1),
(117, 126, 1),
(117, 127, 1),
(117, 129, 1),
(117, 130, 1),
(117, 131, 1),
(117, 132, 1),
(117, 133, 1),
(117, 134, 1),
(117, 135, 1),
(117, 136, 1),
(117, 137, 2),
(117, 139, 1),
(117, 140, 1),
(117, 141, 1),
(119, 5, 1),
(119, 6, 1),
(119, 7, 1),
(119, 10, 1),
(119, 11, 1),
(119, 13, 1),
(119, 14, 1),
(119, 15, 1),
(119, 17, 1),
(119, 18, 1),
(119, 19, 1),
(119, 20, 1),
(119, 23, 1),
(119, 25, 1),
(119, 27, 1),
(119, 28, 1),
(119, 29, 1),
(119, 32, 1),
(119, 33, 1),
(119, 35, 1),
(119, 39, 1),
(119, 43, 1),
(119, 50, 1),
(119, 51, 1),
(119, 52, 1),
(119, 53, 1),
(119, 54, 1),
(119, 58, 1),
(119, 59, 1),
(119, 65, 1),
(119, 66, 1),
(119, 67, 1),
(119, 74, 1),
(119, 76, 1),
(119, 77, 1),
(119, 80, 1),
(119, 83, 1),
(119, 84, 2),
(119, 85, 1),
(119, 86, 1),
(119, 90, 1),
(119, 100, 1),
(119, 104, 2),
(119, 108, 1),
(119, 109, 1),
(119, 111, 1),
(119, 112, 1),
(119, 113, 1),
(119, 115, 1),
(119, 117, 1),
(119, 119, 1),
(119, 120, 1),
(119, 121, 1),
(119, 122, 1),
(119, 126, 1),
(119, 128, 1),
(119, 131, 1),
(119, 132, 1),
(119, 133, 1),
(119, 134, 1),
(119, 136, 1),
(119, 139, 1),
(119, 140, 1),
(120, 1, 1),
(120, 2, 1),
(120, 3, 1),
(120, 5, 1),
(120, 6, 1),
(120, 7, 1),
(120, 8, 1),
(120, 9, 1),
(120, 10, 1),
(120, 11, 1),
(120, 12, 1),
(120, 13, 1),
(120, 14, 1),
(120, 15, 1),
(120, 17, 1),
(120, 19, 1),
(120, 20, 1),
(120, 23, 1),
(120, 24, 1),
(120, 25, 1),
(120, 26, 1),
(120, 27, 1),
(120, 28, 1),
(120, 29, 1),
(120, 32, 1),
(120, 35, 1),
(120, 38, 1),
(120, 39, 1),
(120, 41, 1),
(120, 43, 1),
(120, 44, 1),
(120, 46, 1),
(120, 48, 1),
(120, 49, 1),
(120, 51, 1),
(120, 52, 1),
(120, 53, 1),
(120, 58, 1),
(120, 60, 1),
(120, 61, 1),
(120, 65, 1),
(120, 67, 1),
(120, 69, 1),
(120, 71, 1),
(120, 74, 1),
(120, 76, 1),
(120, 77, 1),
(120, 78, 1),
(120, 79, 1),
(120, 81, 1),
(120, 82, 1),
(120, 83, 1),
(120, 84, 2),
(120, 85, 1),
(120, 86, 1),
(120, 88, 2),
(120, 89, 1),
(120, 90, 1),
(120, 91, 2),
(120, 92, 1),
(120, 93, 1),
(120, 96, 1),
(120, 98, 1),
(120, 99, 2),
(120, 100, 1),
(120, 101, 1),
(120, 103, 1),
(120, 104, 2),
(120, 108, 1),
(120, 109, 1),
(120, 110, 1),
(120, 111, 1),
(120, 112, 1),
(120, 113, 1),
(120, 114, 1),
(120, 115, 1),
(120, 117, 1),
(120, 118, 1),
(120, 119, 1),
(120, 120, 1),
(120, 121, 1),
(120, 122, 1),
(120, 126, 1),
(120, 128, 1),
(120, 130, 1),
(120, 131, 1),
(120, 132, 1),
(120, 133, 1),
(120, 134, 1),
(120, 135, 1),
(120, 136, 1),
(120, 139, 1),
(120, 140, 1),
(124, 25, 1),
(124, 26, 1),
(124, 28, 1),
(124, 30, 1),
(124, 40, 1),
(124, 41, 1),
(124, 42, 1),
(124, 43, 1),
(124, 45, 1),
(124, 47, 1),
(124, 49, 1),
(124, 50, 1),
(124, 51, 1),
(124, 52, 1),
(124, 53, 1),
(124, 54, 1),
(124, 56, 1),
(124, 57, 1),
(124, 59, 1),
(124, 63, 1),
(124, 64, 1),
(124, 66, 1),
(124, 67, 1),
(124, 72, 1),
(124, 73, 1),
(124, 74, 1),
(124, 76, 1),
(124, 77, 1),
(124, 78, 1),
(124, 80, 1),
(124, 82, 1),
(124, 86, 1),
(124, 87, 1),
(124, 89, 1),
(124, 91, 2),
(124, 92, 1),
(124, 98, 1),
(124, 100, 1),
(124, 102, 1),
(124, 105, 1),
(124, 109, 1),
(124, 112, 1),
(124, 113, 1),
(124, 114, 1),
(124, 115, 1),
(124, 116, 1),
(124, 117, 1),
(124, 118, 1),
(124, 119, 1),
(124, 120, 1),
(124, 121, 1),
(124, 124, 1),
(124, 126, 1),
(124, 127, 1),
(124, 128, 1),
(124, 130, 1),
(124, 131, 1),
(124, 132, 1),
(124, 134, 1),
(124, 135, 1),
(124, 136, 1),
(124, 139, 1),
(127, 1, 1),
(127, 5, 1),
(127, 7, 1),
(127, 8, 1),
(127, 9, 1),
(127, 10, 1),
(127, 11, 1),
(127, 13, 1),
(127, 14, 1),
(127, 16, 1),
(127, 17, 1),
(127, 18, 1),
(127, 19, 1),
(127, 20, 1),
(127, 21, 1),
(127, 22, 1),
(127, 25, 1),
(127, 26, 1),
(127, 27, 1),
(127, 28, 1),
(127, 29, 1),
(127, 30, 1),
(127, 31, 1),
(127, 33, 1),
(127, 35, 1),
(127, 36, 1),
(127, 37, 1),
(127, 39, 1),
(127, 40, 1),
(127, 41, 1),
(127, 42, 1),
(127, 43, 1),
(127, 44, 1),
(127, 45, 1),
(127, 46, 1),
(127, 47, 1),
(127, 48, 1),
(127, 49, 1),
(127, 50, 1),
(127, 51, 1),
(127, 52, 1),
(127, 54, 1),
(127, 55, 1),
(127, 56, 1),
(127, 57, 1),
(127, 58, 1),
(127, 60, 1),
(127, 62, 1),
(127, 63, 1),
(127, 64, 1),
(127, 65, 1),
(127, 66, 1),
(127, 67, 1),
(127, 68, 1),
(127, 70, 1),
(127, 71, 1),
(127, 72, 1),
(127, 73, 1),
(127, 74, 1),
(127, 75, 1),
(127, 76, 1),
(127, 77, 1),
(127, 78, 1),
(127, 79, 1),
(127, 80, 1),
(127, 81, 1),
(127, 83, 1),
(127, 84, 2),
(127, 85, 1),
(127, 86, 1),
(127, 87, 1),
(127, 88, 2),
(127, 89, 1),
(127, 92, 1),
(127, 93, 1),
(127, 96, 1),
(127, 98, 1),
(127, 99, 2),
(127, 108, 1),
(127, 109, 1),
(127, 110, 1),
(127, 112, 1),
(127, 113, 1),
(127, 118, 1),
(127, 119, 1),
(127, 120, 1),
(127, 121, 1),
(127, 122, 1),
(127, 123, 1),
(127, 124, 1),
(127, 127, 1),
(127, 132, 1),
(127, 133, 1),
(127, 134, 1),
(127, 135, 1),
(127, 139, 1),
(127, 140, 1),
(127, 141, 1),
(152, 1, 1),
(152, 5, 1),
(152, 7, 1),
(152, 8, 1),
(152, 10, 1),
(152, 11, 1),
(152, 14, 1),
(152, 15, 1),
(152, 17, 1),
(152, 18, 1),
(152, 19, 1),
(152, 24, 1),
(152, 25, 1),
(152, 26, 1),
(152, 27, 1),
(152, 28, 1),
(152, 29, 1),
(152, 32, 1),
(152, 33, 1),
(152, 35, 1),
(152, 36, 1),
(152, 40, 1),
(152, 41, 1),
(152, 43, 1),
(152, 45, 1),
(152, 50, 1),
(152, 51, 1),
(152, 53, 1),
(152, 55, 1),
(152, 57, 1),
(152, 58, 1),
(152, 60, 1),
(152, 61, 1),
(152, 67, 1),
(152, 68, 1),
(152, 69, 1),
(152, 70, 1),
(152, 72, 1),
(152, 73, 1),
(152, 75, 1),
(152, 76, 1),
(152, 77, 1),
(152, 78, 1),
(152, 81, 1),
(152, 86, 1),
(152, 89, 1),
(152, 90, 1),
(152, 96, 1),
(152, 99, 2),
(152, 100, 1),
(152, 102, 1),
(152, 106, 2),
(152, 107, 2),
(152, 109, 1),
(152, 111, 1),
(152, 112, 1),
(152, 114, 1),
(152, 115, 1),
(152, 117, 1),
(152, 118, 1),
(152, 119, 1),
(152, 120, 1),
(152, 122, 1),
(152, 126, 1),
(152, 131, 1),
(152, 132, 1),
(152, 136, 1),
(152, 140, 1),
(152, 141, 1),
(154, 83, 1),
(154, 85, 1),
(154, 86, 1),
(154, 89, 1),
(154, 90, 1),
(154, 92, 1),
(154, 94, 1),
(154, 97, 1),
(154, 98, 1),
(154, 99, 2),
(154, 100, 1),
(154, 101, 1),
(154, 109, 1),
(154, 110, 1),
(154, 111, 1),
(154, 112, 1),
(154, 113, 1),
(154, 115, 1),
(154, 116, 1),
(154, 117, 1),
(154, 118, 1),
(154, 119, 1),
(154, 120, 1),
(154, 121, 1),
(154, 123, 1),
(154, 124, 1),
(154, 125, 1),
(154, 126, 1),
(154, 127, 1),
(154, 128, 1),
(154, 131, 1),
(154, 132, 1),
(154, 134, 1),
(154, 139, 1),
(154, 140, 1),
(155, 1, 1),
(155, 2, 1),
(155, 3, 1),
(155, 5, 1),
(155, 6, 1),
(155, 8, 1),
(155, 10, 1),
(155, 11, 1),
(155, 13, 1),
(155, 14, 1),
(155, 16, 1),
(155, 17, 1),
(155, 18, 1),
(155, 19, 1),
(155, 24, 1),
(155, 25, 1),
(155, 27, 1),
(155, 28, 1),
(155, 29, 1),
(155, 30, 1),
(155, 33, 1),
(155, 34, 1),
(155, 37, 1),
(155, 40, 1),
(155, 43, 1),
(155, 51, 1),
(155, 55, 1),
(155, 56, 1),
(155, 57, 1),
(156, 1, 1),
(156, 2, 1),
(156, 3, 1),
(156, 5, 1),
(156, 6, 1),
(156, 8, 1),
(156, 10, 1),
(156, 11, 1),
(156, 13, 1),
(156, 14, 1),
(156, 16, 1),
(156, 17, 1),
(156, 18, 1),
(156, 19, 1),
(156, 24, 1),
(156, 25, 1),
(156, 26, 1),
(156, 27, 1),
(156, 28, 1),
(156, 29, 1),
(156, 32, 1),
(156, 33, 1),
(156, 34, 1),
(156, 37, 1),
(156, 40, 1),
(156, 43, 1),
(156, 51, 1),
(156, 52, 1),
(156, 53, 1),
(156, 54, 1),
(156, 55, 1),
(156, 56, 1),
(156, 57, 1),
(156, 58, 1),
(156, 59, 1),
(156, 68, 1),
(161, 21, 1),
(161, 22, 1),
(161, 23, 1),
(161, 24, 1),
(161, 25, 1),
(161, 27, 1),
(161, 28, 1),
(161, 29, 1),
(161, 30, 1),
(161, 31, 1),
(161, 32, 1),
(161, 33, 1),
(161, 34, 1),
(161, 35, 1),
(161, 37, 1),
(161, 38, 1),
(161, 39, 1),
(161, 40, 1),
(161, 45, 1),
(161, 49, 1),
(161, 50, 1),
(161, 51, 1),
(161, 52, 1),
(161, 53, 1),
(161, 55, 1),
(161, 57, 1),
(161, 58, 1),
(161, 59, 1),
(161, 60, 1),
(161, 62, 1),
(161, 63, 1),
(161, 64, 1),
(161, 66, 1),
(161, 67, 1),
(161, 69, 1),
(161, 71, 1),
(161, 74, 1),
(161, 75, 1),
(161, 77, 1),
(161, 79, 1),
(161, 80, 1),
(161, 81, 1),
(161, 82, 1),
(161, 83, 1),
(161, 85, 1),
(161, 86, 1),
(161, 88, 2),
(161, 89, 1),
(161, 90, 1),
(161, 92, 1),
(161, 94, 1),
(161, 95, 2),
(161, 97, 1),
(161, 98, 1),
(161, 99, 2),
(161, 100, 1),
(161, 101, 1),
(161, 104, 2),
(161, 107, 2),
(161, 108, 1),
(161, 109, 1),
(161, 110, 1),
(161, 111, 1),
(161, 112, 1),
(161, 113, 1),
(161, 114, 1),
(161, 115, 1),
(161, 116, 1),
(161, 117, 1),
(161, 118, 1),
(161, 119, 1),
(161, 120, 1),
(161, 121, 1),
(161, 122, 1),
(161, 124, 1),
(161, 126, 1),
(161, 127, 1),
(161, 128, 1),
(161, 129, 1),
(161, 131, 1),
(161, 132, 1),
(161, 133, 1),
(161, 134, 1),
(161, 135, 1),
(161, 139, 1),
(161, 140, 1),
(161, 141, 1),
(162, 24, 1),
(162, 27, 1),
(162, 29, 1),
(162, 30, 1),
(162, 34, 1),
(162, 49, 1),
(162, 50, 1),
(162, 51, 1),
(162, 55, 1),
(162, 57, 1),
(162, 59, 1),
(163, 2, 1),
(163, 3, 1),
(163, 4, 1),
(163, 5, 1),
(163, 7, 1),
(163, 8, 1),
(163, 9, 1),
(163, 10, 1),
(163, 11, 1),
(163, 12, 1),
(163, 13, 1),
(163, 14, 1),
(163, 15, 1),
(163, 16, 1),
(163, 17, 1),
(163, 18, 1),
(163, 20, 1),
(163, 22, 1),
(163, 24, 1),
(163, 25, 1),
(163, 31, 1),
(163, 33, 1),
(163, 35, 1),
(163, 36, 1),
(163, 37, 1),
(163, 41, 1),
(163, 49, 1),
(163, 62, 1),
(163, 64, 1),
(163, 117, 1),
(163, 118, 1),
(163, 120, 1),
(163, 121, 1),
(163, 123, 1),
(163, 127, 1),
(163, 130, 1),
(163, 131, 1),
(163, 132, 1),
(163, 134, 1),
(163, 135, 1),
(163, 136, 1),
(163, 141, 1),
(164, 1, 1),
(164, 2, 1),
(164, 5, 1),
(164, 6, 1),
(164, 7, 1),
(164, 8, 1),
(164, 9, 1),
(164, 10, 1),
(164, 12, 1),
(164, 13, 1),
(164, 14, 1),
(164, 15, 1),
(164, 16, 1),
(164, 17, 1),
(164, 18, 1),
(164, 19, 1),
(164, 21, 1),
(164, 24, 1),
(164, 25, 1),
(164, 26, 1),
(164, 27, 1),
(164, 28, 1),
(164, 29, 1),
(164, 30, 1),
(164, 33, 1),
(164, 34, 1),
(164, 35, 1),
(164, 36, 1),
(164, 40, 1),
(164, 41, 1),
(164, 42, 1),
(164, 43, 1),
(164, 44, 1),
(164, 45, 1),
(164, 48, 1),
(164, 49, 1),
(164, 50, 1),
(164, 51, 1),
(164, 53, 1),
(164, 54, 1),
(164, 55, 1),
(164, 56, 1),
(164, 57, 1),
(164, 58, 1),
(164, 59, 1),
(164, 60, 1),
(164, 61, 1),
(164, 63, 1),
(164, 64, 1),
(164, 65, 1),
(164, 66, 1),
(164, 67, 1),
(164, 68, 1),
(164, 69, 1),
(164, 70, 1),
(164, 72, 1),
(164, 75, 1),
(164, 76, 1),
(164, 77, 1),
(164, 78, 1),
(164, 79, 1),
(164, 80, 1),
(164, 81, 1),
(164, 82, 1),
(164, 83, 1),
(164, 84, 2),
(164, 85, 1),
(164, 86, 1),
(164, 87, 1),
(164, 89, 1),
(164, 90, 1),
(164, 91, 2),
(164, 96, 1),
(164, 97, 1),
(164, 98, 1),
(164, 99, 2),
(164, 100, 1),
(164, 101, 1),
(164, 102, 1),
(164, 107, 2),
(164, 108, 1),
(164, 109, 1),
(164, 110, 1),
(164, 111, 1),
(164, 113, 1),
(164, 114, 1),
(164, 115, 1),
(164, 116, 1),
(164, 117, 1),
(164, 119, 1),
(164, 120, 1),
(164, 121, 1),
(164, 122, 1),
(164, 123, 1),
(164, 126, 1),
(164, 127, 1),
(164, 128, 1),
(164, 129, 1),
(164, 130, 1),
(164, 132, 1),
(164, 133, 1),
(164, 134, 1),
(164, 136, 1),
(164, 139, 1),
(164, 140, 1),
(164, 141, 1),
(165, 2, 1),
(165, 5, 1),
(165, 6, 1),
(165, 7, 1),
(165, 8, 1),
(165, 9, 1),
(165, 10, 1),
(165, 11, 1),
(165, 12, 1),
(165, 14, 1),
(165, 18, 1),
(165, 19, 1),
(165, 26, 1),
(165, 29, 1),
(165, 32, 1),
(165, 33, 1),
(165, 35, 1),
(165, 52, 1),
(165, 53, 1),
(165, 54, 1),
(165, 55, 1),
(165, 56, 1),
(165, 57, 1),
(165, 58, 1),
(165, 59, 1),
(165, 72, 1),
(165, 74, 1),
(165, 77, 1),
(165, 100, 1),
(165, 102, 1),
(165, 109, 1),
(165, 111, 1),
(165, 113, 1),
(165, 115, 1),
(165, 118, 1),
(165, 119, 1),
(165, 120, 1),
(165, 122, 1),
(165, 123, 1),
(165, 124, 1),
(165, 125, 1),
(165, 126, 1),
(165, 127, 1),
(165, 132, 1),
(165, 133, 1),
(165, 134, 1),
(165, 135, 1),
(165, 139, 1),
(165, 140, 1),
(171, 26, 1),
(171, 27, 1),
(171, 28, 1),
(171, 29, 1),
(171, 30, 1),
(171, 31, 1),
(171, 32, 1),
(171, 33, 1),
(171, 34, 1),
(171, 35, 1),
(171, 36, 1),
(171, 37, 1),
(171, 38, 1),
(171, 39, 1),
(171, 40, 1),
(171, 42, 1),
(171, 44, 1),
(171, 45, 1),
(171, 46, 1),
(171, 47, 1),
(171, 48, 1),
(171, 49, 1),
(171, 50, 1),
(171, 51, 1),
(171, 52, 1),
(171, 53, 1),
(171, 55, 1),
(171, 57, 1),
(171, 58, 1),
(171, 59, 1),
(171, 60, 1),
(171, 61, 1),
(171, 62, 1),
(171, 63, 1),
(171, 64, 1),
(171, 65, 1),
(171, 66, 1),
(171, 67, 1),
(171, 68, 1),
(171, 69, 1),
(171, 70, 1),
(171, 71, 1),
(171, 72, 1),
(171, 74, 1),
(171, 75, 1),
(171, 76, 1),
(171, 77, 1),
(171, 78, 1),
(171, 79, 1),
(171, 80, 1),
(171, 81, 1),
(171, 82, 1),
(171, 83, 1),
(171, 84, 2),
(171, 85, 1),
(171, 86, 1),
(171, 88, 2),
(171, 89, 1),
(171, 90, 1),
(171, 92, 1),
(171, 93, 1),
(171, 94, 1),
(171, 95, 2),
(171, 96, 1),
(171, 97, 1),
(171, 98, 1),
(171, 99, 2),
(171, 100, 1),
(171, 101, 1),
(171, 102, 1),
(171, 103, 1),
(171, 104, 2),
(171, 107, 2),
(171, 108, 1),
(171, 109, 1),
(171, 110, 1),
(171, 111, 1),
(171, 112, 1),
(171, 113, 1),
(171, 114, 1),
(171, 115, 1),
(171, 116, 1),
(171, 117, 1),
(171, 118, 1),
(171, 119, 1),
(171, 120, 1),
(171, 121, 1),
(171, 122, 1),
(171, 124, 1),
(171, 126, 1),
(171, 127, 1),
(171, 128, 1),
(171, 129, 1),
(171, 131, 1),
(171, 132, 1),
(171, 133, 1),
(171, 134, 1),
(171, 135, 1),
(171, 136, 1),
(171, 138, 2),
(171, 139, 1),
(171, 140, 1),
(171, 141, 1),
(172, 78, 1),
(172, 81, 1),
(172, 85, 1),
(172, 86, 1),
(172, 87, 1),
(172, 89, 1),
(172, 90, 1),
(172, 92, 1),
(172, 93, 1),
(172, 94, 1),
(172, 96, 1),
(172, 97, 1),
(172, 98, 1),
(172, 101, 1),
(172, 102, 1),
(172, 105, 1),
(172, 109, 1),
(172, 110, 1),
(172, 111, 1),
(172, 112, 1),
(172, 113, 1),
(172, 114, 1),
(172, 115, 1),
(172, 117, 1),
(172, 118, 1),
(172, 119, 1),
(172, 120, 1),
(172, 122, 1),
(172, 123, 1),
(172, 124, 1),
(172, 126, 1),
(172, 127, 1),
(172, 128, 1),
(172, 130, 1),
(172, 131, 1),
(172, 132, 1),
(172, 134, 1),
(172, 135, 1),
(172, 136, 1),
(172, 139, 1),
(172, 140, 1),
(172, 141, 1),
(173, 40, 1),
(173, 41, 1),
(173, 42, 1),
(173, 43, 1),
(173, 44, 1),
(173, 45, 1),
(173, 46, 1),
(173, 47, 1),
(173, 48, 1),
(173, 49, 1),
(173, 50, 1),
(173, 51, 1),
(173, 52, 1),
(173, 54, 1),
(173, 55, 1),
(173, 56, 1),
(173, 57, 1),
(173, 58, 1),
(173, 60, 1),
(173, 61, 1),
(173, 62, 1),
(173, 63, 1),
(173, 64, 1),
(173, 65, 1),
(173, 66, 1),
(173, 67, 1),
(173, 68, 1),
(173, 69, 1),
(173, 70, 1),
(173, 71, 1),
(173, 72, 1),
(173, 73, 1),
(173, 74, 1),
(173, 75, 1),
(173, 76, 1),
(173, 78, 1),
(173, 79, 1),
(173, 80, 1),
(173, 81, 1),
(173, 83, 1),
(173, 84, 2),
(173, 85, 1),
(173, 86, 1),
(173, 87, 1),
(173, 88, 2),
(173, 89, 1),
(173, 92, 1),
(173, 93, 1),
(173, 96, 1),
(173, 98, 1),
(173, 99, 2),
(173, 100, 1),
(173, 101, 1),
(173, 102, 1),
(173, 103, 1),
(173, 104, 2),
(173, 105, 1),
(173, 106, 2),
(173, 108, 1),
(173, 109, 1),
(173, 110, 1),
(173, 111, 1),
(173, 112, 1),
(173, 113, 1),
(173, 118, 1),
(173, 119, 1),
(173, 120, 1),
(173, 121, 1),
(173, 122, 1),
(173, 123, 1),
(173, 124, 1),
(173, 132, 1),
(173, 133, 1),
(173, 134, 1),
(173, 135, 1),
(173, 139, 1),
(173, 140, 1),
(173, 141, 1),
(175, 67, 1),
(175, 69, 1),
(175, 71, 1),
(175, 74, 1),
(175, 75, 1),
(175, 76, 1),
(175, 77, 1),
(175, 78, 1),
(175, 80, 1),
(175, 81, 1),
(175, 83, 1),
(175, 84, 2),
(175, 86, 1),
(175, 89, 1),
(175, 90, 1),
(175, 119, 1),
(175, 120, 1),
(175, 122, 1),
(175, 123, 1),
(175, 124, 1),
(175, 125, 1),
(175, 126, 1),
(175, 129, 1),
(175, 130, 1),
(175, 135, 1),
(175, 136, 1),
(175, 137, 2),
(175, 139, 1),
(176, 109, 1),
(176, 110, 1),
(176, 111, 1),
(176, 112, 1),
(176, 113, 1),
(176, 114, 1),
(176, 115, 1),
(176, 116, 1),
(176, 118, 1),
(176, 119, 1),
(176, 120, 1),
(176, 121, 1),
(176, 122, 1),
(176, 123, 1),
(176, 124, 1),
(176, 125, 1),
(176, 127, 1),
(176, 131, 1),
(176, 132, 1),
(176, 133, 1),
(176, 134, 1),
(176, 139, 1),
(176, 140, 1),
(177, 109, 1),
(177, 110, 1),
(177, 111, 1),
(177, 112, 1),
(177, 113, 1),
(177, 114, 1),
(177, 115, 1),
(177, 116, 1),
(177, 118, 1),
(177, 119, 1),
(177, 120, 1),
(177, 121, 1),
(177, 122, 1),
(177, 123, 1),
(177, 124, 1),
(177, 125, 1),
(177, 127, 1),
(177, 131, 1),
(177, 132, 1),
(177, 133, 1),
(177, 134, 1),
(177, 139, 1),
(177, 140, 1),
(178, 109, 1),
(178, 110, 1),
(178, 112, 1),
(178, 113, 1),
(178, 114, 1),
(178, 115, 1),
(178, 116, 1),
(178, 117, 1),
(178, 118, 1),
(178, 119, 1),
(178, 121, 1),
(178, 123, 1),
(178, 125, 1),
(178, 126, 1),
(178, 127, 1),
(178, 131, 1),
(178, 132, 1),
(178, 134, 1),
(178, 140, 1),
(184, 110, 1),
(184, 113, 1),
(184, 114, 1),
(184, 118, 1),
(184, 119, 1),
(184, 120, 1),
(184, 121, 1),
(184, 122, 1),
(184, 123, 1),
(184, 125, 1),
(184, 126, 1),
(184, 127, 1),
(184, 128, 1),
(184, 129, 1),
(184, 131, 1),
(184, 132, 1),
(184, 133, 1),
(184, 134, 1),
(184, 139, 1),
(184, 140, 1),
(184, 141, 1),
(186, 109, 1),
(186, 110, 1),
(186, 112, 1),
(186, 113, 1),
(187, 109, 1),
(187, 110, 1),
(187, 111, 1),
(187, 113, 1),
(187, 114, 1),
(187, 116, 1),
(187, 117, 1),
(187, 118, 1),
(187, 119, 1),
(187, 120, 1),
(187, 122, 1),
(187, 123, 1),
(187, 124, 1),
(187, 125, 1),
(187, 126, 1),
(187, 127, 1),
(187, 130, 1),
(187, 132, 1),
(187, 133, 1),
(187, 134, 1),
(187, 135, 1),
(187, 136, 1),
(187, 138, 2),
(187, 139, 1),
(187, 140, 1),
(187, 141, 1),
(188, 109, 1),
(188, 110, 1),
(188, 112, 1),
(188, 113, 1),
(188, 114, 1),
(188, 115, 1),
(188, 116, 1),
(188, 117, 1),
(188, 118, 1),
(188, 119, 1),
(188, 120, 1),
(188, 121, 1),
(188, 122, 1),
(188, 123, 1),
(188, 125, 1),
(188, 126, 1),
(188, 127, 1),
(188, 128, 1),
(188, 131, 1),
(188, 132, 1),
(188, 134, 1),
(188, 139, 1),
(188, 140, 1),
(188, 141, 1);

-- --------------------------------------------------------

--
-- Structure de la table `cdc_locaux`
--

CREATE TABLE `cdc_locaux` (
  `idloc` int(11) NOT NULL COMMENT 'ID du local de répétition',
  `local` varchar(40) NOT NULL COMMENT 'Désignation du local',
  `cpostal` char(5) NOT NULL COMMENT 'Code postal du local',
  `ville` varchar(25) NOT NULL COMMENT 'Ville du local'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table des locaux de répétitions';

--
-- Déchargement des données de la table `cdc_locaux`
--

INSERT INTO `cdc_locaux` (`idloc`, `local`, `cpostal`, `ville`) VALUES
(1, 'Salle Mont Charvin', '73800', 'Cruet'),
(2, '', '73130', 'Sainte-Marie-de-Cuines');

-- --------------------------------------------------------

--
-- Structure de la table `cdc_repetitions`
--

CREATE TABLE `cdc_repetitions` (
  `idrep` int(11) NOT NULL COMMENT 'ID de la répétition',
  `drepet` date NOT NULL COMMENT 'Date de la répétition'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table des répétitions';

--
-- Déchargement des données de la table `cdc_repetitions`
--

INSERT INTO `cdc_repetitions` (`idrep`, `drepet`) VALUES
(1, '2015-01-09'),
(2, '2015-01-16'),
(3, '2015-01-23'),
(4, '2015-02-13'),
(5, '2015-02-27'),
(6, '2015-03-06'),
(7, '2015-03-13'),
(8, '2015-03-27'),
(9, '2015-04-03'),
(10, '2015-04-10'),
(11, '2015-04-17'),
(12, '2015-04-24'),
(13, '2015-04-30'),
(14, '2015-05-07'),
(15, '2015-05-15'),
(16, '2015-05-22'),
(17, '2015-05-29'),
(18, '2015-06-05'),
(19, '2015-06-12'),
(20, '2015-06-26'),
(21, '2015-07-10'),
(22, '2015-07-17'),
(23, '2015-08-28'),
(24, '2015-09-04'),
(25, '2015-09-11'),
(26, '2015-09-25'),
(27, '2015-10-02'),
(28, '2015-10-09'),
(29, '2015-10-16'),
(30, '2015-10-23'),
(31, '2015-10-30'),
(32, '2015-11-10'),
(33, '2015-11-13'),
(34, '2015-11-17'),
(35, '2015-11-20'),
(36, '2015-11-27'),
(37, '2015-12-04'),
(38, '2015-12-11'),
(39, '2015-12-18'),
(40, '2016-01-08'),
(41, '2016-01-15'),
(42, '2016-01-22'),
(43, '2016-01-29'),
(44, '2016-02-05'),
(45, '2016-02-12'),
(46, '2016-02-26'),
(47, '2016-02-26'),
(48, '2016-03-04'),
(49, '2016-03-18'),
(50, '2016-03-25'),
(51, '2016-04-01'),
(52, '2016-04-08'),
(53, '2016-04-15'),
(54, '2016-04-22'),
(55, '2016-04-29'),
(56, '2016-05-13'),
(57, '2016-05-20'),
(58, '2016-05-27'),
(59, '2016-06-03'),
(60, '2016-06-17'),
(61, '2016-06-24'),
(62, '2016-07-12'),
(63, '2016-07-22'),
(64, '2016-07-29'),
(65, '2016-08-26'),
(66, '2016-09-09'),
(67, '2016-09-16'),
(68, '2016-09-23'),
(69, '2016-09-30'),
(70, '2016-10-07'),
(71, '2016-10-14'),
(72, '2016-10-21'),
(73, '2016-10-28'),
(74, '2016-11-04'),
(75, '2016-11-11'),
(76, '2016-11-18'),
(77, '2016-11-25'),
(78, '2016-12-02'),
(79, '2016-12-09'),
(80, '2016-12-16'),
(81, '2017-01-06'),
(82, '2017-01-13'),
(83, '2017-01-27'),
(84, '2017-02-03'),
(85, '2017-02-10'),
(86, '2017-02-17'),
(87, '2017-02-24'),
(88, '2017-03-03'),
(89, '2017-03-10'),
(90, '2017-03-17'),
(91, '2017-04-07'),
(92, '2017-04-14'),
(93, '2017-04-21'),
(94, '2017-04-28'),
(95, '2017-05-05'),
(96, '2017-05-12'),
(97, '2017-05-19'),
(98, '2017-05-26'),
(99, '2017-06-02'),
(100, '2017-06-09'),
(101, '2017-06-16'),
(102, '2017-06-23'),
(103, '2017-06-30'),
(104, '2017-07-07'),
(105, '2017-07-21'),
(106, '2017-07-28'),
(107, '2017-08-04'),
(108, '2017-08-25'),
(109, '2017-09-29'),
(110, '2017-09-01'),
(111, '2017-09-08'),
(112, '2017-09-15'),
(113, '2017-10-06'),
(114, '2017-10-13'),
(115, '2017-10-20'),
(116, '2017-10-27'),
(117, '2017-11-03'),
(118, '2017-11-10'),
(119, '2017-11-17'),
(120, '2017-11-24'),
(121, '2017-12-01'),
(122, '2017-12-08'),
(123, '2017-12-15'),
(124, '2018-01-05'),
(125, '2018-01-12'),
(126, '2018-01-19'),
(127, '2018-01-26'),
(128, '2018-02-09'),
(129, '2018-02-16'),
(130, '2018-02-23'),
(131, '2018-03-09'),
(132, '2018-03-23'),
(133, '2018-03-30'),
(134, '2018-04-06'),
(135, '2018-04-13'),
(136, '2018-04-20'),
(137, '2018-02-28'),
(138, '2018-04-25'),
(139, '2018-04-27'),
(140, '2018-05-04'),
(141, '2018-05-18');

--
-- Index pour les tables déchargées
--

--
-- Index pour la table `cdc_assister`
--
ALTER TABLE `cdc_assister`
  ADD PRIMARY KEY (`idpers`,`idrep`,`idloc`);

--
-- Index pour la table `cdc_locaux`
--
ALTER TABLE `cdc_locaux`
  ADD PRIMARY KEY (`idloc`);

--
-- Index pour la table `cdc_repetitions`
--
ALTER TABLE `cdc_repetitions`
  ADD PRIMARY KEY (`idrep`);

--
-- AUTO_INCREMENT pour les tables déchargées
--

--
-- AUTO_INCREMENT pour la table `cdc_locaux`
--
ALTER TABLE `cdc_locaux`
  MODIFY `idloc` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID du local de répétition', AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT pour la table `cdc_repetitions`
--
ALTER TABLE `cdc_repetitions`
  MODIFY `idrep` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID de la répétition', AUTO_INCREMENT=142;COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      1. jordane45 Modérateur
        Ceci fonctionne parfaitement 
        SELECT YEAR(R.drepet) AS annee, 
       COUNT(R.idrep) AS nbrep
FROM cdc_repetitions R
LEFT JOIN cdc_assister A ON A.idrep = R.idrep
LEFT JOIN cdc_locaux L ON L.idloc = A.idloc
WHERE  A.idloc = 2
GROUP BY annee
ORDER BY annee


        Ou si tu veux "décomposer", tu peux aussi faire: 
        SELECT T.* 
   ,COUNT(T.idrep) AS nbrep
FROM (
SELECT YEAR(R.drepet) AS annee
	,R.idrep
	,A.idloc
FROM cdc_repetitions R
LEFT JOIN cdc_assister A ON A.idrep = R.idrep
LEFT JOIN cdc_locaux L ON L.idloc = A.idloc
WHERE A.idloc = 2
	) T
GROUP BY T.annee
ORDER BY T.annee


        Ce qui, au final, retourne bien exactement le même résultat que ta requête initiale 
        SELECT YEAR(cdc_repetitions.drepet) AS annee, COUNT(cdc_repetitions.idrep) AS nbrep
FROM cdc_repetitions,cdc_assister,cdc_locaux
WHERE cdc_locaux.idloc = cdc_assister.idloc
	AND cdc_repetitions.idrep = cdc_assister.idrep
	AND cdc_assister.idloc = 2
GROUP BY annee
ORDER BY annee
        1. LaChaux78 Auteur
          Bonjour
          Le résultat n'est toujours pas bon. En fait j'ai regardé et il se trouve par exemple en 2018 nous avons eu 2 répétitions "idrep 137" et "idrep 138" avec la condition "idloc est à 2" et le résultat me retourne le tableau suivant.
          Année Nombre
          2018 8
          1. LaChaux78 Auteur
            J'ai essayé dans phpmyadmin et voici le résultat
            annee idrep idloc nbrep
            2017 88 2 88
            2018 138 2 8
            1. LaChaux78 Auteur
              En fait j'ai analyser le résultat de la requète et je me suis aperçu que nbrep corresponds au nombre de personnes présente pour chaque année au lieu du nombre de répétitions, c'est à dire 88 et 8
              1. jordane45 Modérateur
                Si c'est le nombre de répétitions que tu veux uniquement... pas besoin de jointure ! 
                SELECT YEAR(R.drepet) AS annee, 
       COUNT(R.idrep) AS nbrep
FROM cdc_repetitions R
GROUP BY YEAR(R.drepet) 
ORDER BY YEAR(R.drepet)
            2. LaChaux78 Auteur
              oui c'est bien le nombre de répétitions par année que je souhaite mais avec une condition:
              WHERE cdc_assister.idloc = 2
              J'ai essayer mais cela ne le fait pas.
              1. jordane45 Modérateur
                Ta base est mal conçue...
                Pourquoi avoir placé le idloc dans la table cdc_assister ?
                La localisation doit bien correspondre à la répétition....
                Je doute que pour une même répétition tu aies des gens dans différentes salles...... non ?
              2. jordane45 Modérateur
                Si vraiment tu tiens à conserver tes tables comme ça ( ça serait une drôle d'idée....) voici la requête que tu sembles vouloir obtenir 
                SELECT T.* 
   ,COUNT(T.idrep) AS nbrep
FROM (
	SELECT YEAR(R.drepet) AS annee
		,R.idrep
		,A.idloc
	FROM cdc_repetitions R
	LEFT JOIN cdc_assister A ON A.idrep = R.idrep
	LEFT JOIN cdc_locaux L ON L.idloc = A.idloc
	WHERE A.idloc = 2
	GROUP BY R.idrep
		) T
GROUP BY T.annee
ORDER BY T.annee
            3. LaChaux78 Auteur
              Oui tu as raison j'aurai pu mettrre idloc dans la table cdc_repetitions pas bien réfléchis. Je vais voir si je peux faire cette modification sans trop de risque. Par contre ta requète fonctionne c'est bien ce que je souhaitais.
              Je ne comprends pas SELECT T.* si tu peux m'expliquer.
              Je te remercie.
              1. jordane45 Modérateur
                "T" c'est l'alias de la table dérivée ( provenant de la sous-requête utilisée dans le FROM )
                • c'est pour récupérer TOUS les champs ( à savoir.. ceux qui sont dans le SELECT de la table dérivée )

                Ici : 
                SELECT YEAR(R.drepet) AS annee
		,R.idrep
		,A.idloc
            4. LaChaux78 Auteur
              Bonjour,
              J'ai modifié mes deux tables comme tu me l'a dit et du coup le requète est beaucoup plus simple .
              Je te remercie.

              Discussions similaires

              Téléphone en condition optimale...?
              Elka_3079

              2 réponses
              Où est la touche SHIFT sur mon clavier d'ordi ?
              joe!

              18 réponses
              enlever mode sécurisé huawei
              faihere26

              1 réponse
              Changer la couleur d'une cellule selon son texte
              fantomex

              5 réponses
              Excel et cellules en couleur si texte
              Jimmy

              10 réponses