title: Display number of town visited for each driver
body: <p>I'm actually trying to have a number of time that a driver visited a town.
  I'm close to have the good result, but I need a little bit help to achieve this.</p>
  
  <p>So here it is my database</p>
  
  <pre><code>TABLE_NAME  COLUMN_NAME      DATA_TYPE
  ______________________________________
  camion      immatriculation  char           &lt;--- PK
  camion      annee            date
  camion      kilometrage      float
  _____________________________________
  conducteur  id_conducteur   int            &lt;--- PK
  conducteur  nom             varchar
  conducteur  date_embauche   date
  _______________________________________
  trajet      id_conducteur   int            &lt;--- PK, FK REFERENCES conducteur(id_conducteur)
  trajet      immatriculation char           &lt;--- PK, FK REFERENCES camion(immatriculation)
  trajet      date_trajet     datetime       &lt;--- PK
  trajet      ville_depart    int            &lt;--- FK REFERENCES ville(id)
  trajet      ville_arrivee   int            &lt;--- FK REFERENCES ville(id)
  ______________________________________
  ville       id              int            &lt;--- PK
  ville       libelle         varchar
  </code></pre>
  
  <p>Then I want to know by example how many time a driver is gone to every town that he visited</p>
  
  <p>So I tried this:</p>
  
  <pre><code>SELECT * FROM
  (
      SELECT * FROM
      (
          SELECT c.nom, vd.libelle, COUNT(t.ville_depart) AS 'nbr_ville'
          FROM trajet t
          JOIN conducteur c ON c.id_conducteur = t.id_conducteur
          JOIN ville vd ON vd.id = t.ville_depart
          GROUP BY GROUPING SETS
          (
              (vd.libelle,vd.id,c.nom)
          )
      ) AS depart
      UNION ALL
      SELECT * FROM
      (
          SELECT c.nom, va.libelle, COUNT(t.ville_arrivee) AS 'nbr_ville'
          FROM trajet t
          JOIN conducteur c ON c.id_conducteur = t.id_conducteur
          JOIN ville va ON va.id = t.ville_arrivee
          GROUP BY GROUPING SETS
          (
              (va.libelle,va.id,c.nom)
          )
      ) AS arrivee
  ) AS touteville
  ORDER BY touteville.nom,touteville.libelle
  ;
  </code></pre>
  
  <p>(I use grouping sets only for learning to use it, but I know that is could be replace by a simple 'group by')</p>
  
  <p>So here it is what I have as a result:</p>
  
  <pre><code>nom     libelle     nbr_ville
  BERNARD Avignon     2
  BERNARD Avignon     1
  BERNARD Bordeaux    1
  BERNARD Bordeaux    1
  BERNARD Nancy       3
  BERNARD Nancy       3
  BERNARD Paris       1
  BERNARD Paris       1
  BERNARD Strasbourg  1
  DUPONT  Bordeaux    1
  DUPONT  Bordeaux    1
  DUPONT  Paris       1
  DUPONT  Paris       1
  DUPONT  Strasbourg  1
  DUPONT  Strasbourg  1
  MAXIME  Lyon        1
  MAXIME  Lyon        1
  MAXIME  Paris       1
  MAXIME  Paris       1
  MAXIME  Strasbourg  1
  MAXIME  Toulouse    1
  THIERY  Avignon     1
  THIERY  Avignon     2
  THIERY  Bordeaux    1
  THIERY  Marseille   1
  THIERY  Marseille   1
  THIERY  Nancy       1
  THIERY  Nancy       1
  THIERY  Paris       2
  THIERY  Paris       1
  THIERY  Strasbourg  1
  </code></pre>
  
  <p>But I wish I could have <strong>group</strong> all the result by the column named 'libelle   ' which are the name of the town.</p>
  
  <p>How could I do that?</p>
  
  <p>I try to just do a GROUP BY libelle on the 'touteville' virtual table, but if I want to do that I have to add 'nbr_ville' and 'nom' to the group by or add it into a aggregate function, as SQL Server told me.</p>
  
  <p>So what I want to have is: </p>
  
  <pre><code>nom     libelle     nbr_ville
  BERNARD Avignon     3
  BERNARD Bordeaux    2
  BERNARD Nancy       6
  </code></pre>
  
  <p>Instead of</p>
  
  <pre><code>nom     libelle  nbr_ville
  BERNARD Avignon  2
  BERNARD Avignon  1
  BERNARD Bordeaux 1
  BERNARD Bordeaux 1
  BERNARD Nancy    3
  BERNARD Nancy    3
  </code></pre>