Display number of town visited for each driver

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.

So here it is my database

TABLE_NAME  COLUMN_NAME      DATA_TYPE
______________________________________
camion      immatriculation  char           <--- PK
camion      annee            date
camion      kilometrage      float
_____________________________________
conducteur  id_conducteur   int            <--- PK
conducteur  nom             varchar
conducteur  date_embauche   date
_______________________________________
trajet      id_conducteur   int            <--- PK, FK REFERENCES conducteur(id_conducteur)
trajet      immatriculation char           <--- PK, FK REFERENCES camion(immatriculation)
trajet      date_trajet     datetime       <--- PK
trajet      ville_depart    int            <--- FK REFERENCES ville(id)
trajet      ville_arrivee   int            <--- FK REFERENCES ville(id)
______________________________________
ville       id              int            <--- PK
ville       libelle         varchar

Then I want to know by example how many time a driver is gone to every town that he visited

So I tried this:

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
;

(I use grouping sets only for learning to use it, but I know that is could be replace by a simple ‘group by’)

So here it is what I have as a result:

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

But I wish I could have group all the result by the column named ‘libelle ‘ which are the name of the town.

How could I do that?

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.

So what I want to have is:

nom     libelle     nbr_ville
BERNARD Avignon     3
BERNARD Bordeaux    2
BERNARD Nancy       6

Instead of

nom     libelle  nbr_ville
BERNARD Avignon  2
BERNARD Avignon  1
BERNARD Bordeaux 1
BERNARD Bordeaux 1
BERNARD Nancy    3
BERNARD Nancy    3

Answer

Pretty sure you could simplify your entire query to something like this.

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 
             OR vd.id = t.ville_arrivee
group by c.nom
    , vd.libelle
order by c.nom
    , vd.libelle

Advertisement

Answer