Subquery : trying to add 1 more column

Question

I'm novice with SQL. Actually, I'm trying to add a new column to this :

SELECT ville_departement, COUNT(*) FROM villes_france_free 
GROUP BY ville_departement
HAVING ville_departement IN
(SELECT ville_departement FROM departement
INNER JOIN villes_france_free 
ON departement.departement_code = villes_france_free.ville_departement);

I have the number of cities from each departements. But in addition of departement_code, I want to show departement_name on the final result

Problem : Obviously, I can't confront 1 column with 2.

The link between departement_code and departement_name is defined as : there is one departement_code for several departement_name.

Thanks for your help !! :)

Léandre

Answer 1

To update my awser to the other post

SELECT 
      departement_code, 
      departement_nom,
      COUNT(*) as NombreVilles
FROM 
      villes_france_free as ville inner join
      departement on departement.departement_code = ville.ville_departement
GROUP BY 
      departement_code, 
      departement_nom
HAVING 
      ville_departement IN ( Your filter ) --- No need to put a subquery select in here