title: Selecting number of adherent by passport type?
body: <p>I have created a table:</p>
  
  <pre><code>
  
  CREATE TABLE passeport 
  (
  numero_passeport INTEGER, 
  IDadherent INTEGER, 
  couleur VARCHAR(6), 
  specialite VARCHAR(25), 
  date_validation DATE, 
  CONSTRAINT pkfh PRIMARY KEY (numero_passeport));
  
  INSERT INTO passeport VALUES (1, 001, 'Blanc', null, '1995/02/18');
  INSERT INTO passeport VALUES (2, 002, 'Blanc', null, '2020/02/25');
  INSERT INTO passeport VALUES (3, 003, 'Blanc', null, '2015/04/17');
  INSERT INTO passeport VALUES (4, 004, 'Blanc', null, '2012/12/21');
  INSERT INTO passeport VALUES (5, 005, 'Blanc', null, '1996/05/02');
  INSERT INTO passeport VALUES (6, 006, 'Blanc', null, '2009/09/01');
  
  INSERT INTO passeport VALUES (7, 001, 'Jaune', null, '1998/11/20');
  INSERT INTO passeport VALUES (8, 003, 'Jaune', null, '2018/04/07');
  INSERT INTO passeport VALUES (9, 004, 'Jaune', null, '2013/02/21');
  INSERT INTO passeport VALUES (10, 005, 'Jaune', null, '2018/05/02');
  INSERT INTO passeport VALUES (11, 006, 'Jaune', null, '2010/03/08');
  
  INSERT INTO passeport VALUES (12, 001, 'Orange', null, '2000/03/23');
  INSERT INTO passeport VALUES (13, 003, 'Orange', null, '2019/04/05');
  INSERT INTO passeport VALUES (14, 004, 'Orange', null, '2014/08/06');
  INSERT INTO passeport VALUES (15, 005, 'Orange', null, '2020/01/02');
  INSERT INTO passeport VALUES (16, 006, 'Orange', null, '2017/01/01');
  
  INSERT INTO passeport VALUES (17, 003, 'Vert', null, '2020/06/21');
  INSERT INTO passeport VALUES (18, 004, 'Vert', null, '2016/01/15');
  INSERT INTO passeport VALUES (19, 006, 'Vert', null, '2009/09/01');
  
  INSERT INTO passeport VALUES (20, 004, 'Bleu', null, '2017/11/14');
  INSERT INTO passeport VALUES (21, 006, 'Bleu', null, '2009/09/01');
  
  INSERT INTO passeport VALUES (21, 004, 'Violet', null, '2018/10/14');
  INSERT INTO passeport VALUES (22, 006, 'Violet', null, '2009/09/01');
  
  INSERT INTO passeport VALUES (23, 004, 'Rouge', 'Performance', '2018/11/18');
  INSERT INTO passeport VALUES (24, 006, 'Rouge', 'Grands espaces', '2011/05/02');
  
  INSERT INTO passeport VALUES (25, 004, 'Rouge', 'Performance', '2019/12/17');
  INSERT INTO passeport VALUES (26, 006, 'Rouge', 'Grands espaces', '2011/08/11');
  
  
  INSERT INTO passeport VALUES (27, 006, 'Noir', 'Bloc', '2012/05/13');
  INSERT INTO passeport VALUES (28, 006, 'Noir', 'voie', '2012/02/06');
  INSERT INTO passeport VALUES (29, 006, 'Noir', 'grande voie', '2015/02/04');
  INSERT INTO passeport VALUES (30, 006, 'Noir', 'compétition bloc', '2018/04/17');
  INSERT INTO passeport VALUES (31, 006, 'Noir', 'compétition voie', '2019/12/29');
  INSERT INTO passeport VALUES (32, 006, 'Noir','compétition grande voie', '2020/02/18');
  
  </code></pre>
  
  <p>I'm attempting to make a query to be able to know how many adherents hold each type of passport. </p>
  
  <p>The thing here is that passeport "noir" and "rouge" have various subcategories.</p>
  
  <p>Each time I attempt to run my current query or its variations my program counts the same indvidual holding different subcategories of the passport as different individuals:</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT COUNT(*), couleur FROM passeport GROUP BY couleur;
  
  </code></pre>
  
  <p>Result</p>
  
  <pre><code>+----------+---------+
  | COUNT(*) | couleur |
  +----------+---------+
  |        6 | Blanc   |
  |        5 | Jaune   |
  |        5 | Orange  |
  |        3 | Vert    |
  |        2 | Bleu    |
  |        4 | Rouge   |
  |        6 | Noir    |
  +----------+---------+
  </code></pre>
  
  <p>I should expect to get 1 individual (IDadherent 006) for the passeport noir.</p>
  
  <p>Can anyone help?</p>