Compter sur une table à partir d’une autre

Bonjour,

J’ai une base avec deux tables :

La table utilisateur avec Nom, prénom et localisation,

La table Commune avec les noms des villes sous NCCENR.

Je voudrais compter le nombre de fois qu’une ville est associée à un utilisateur.

NCCENR COUNT

Paris 12

Nantes 33

...

PS : la localisation est rentrée par l’utilisateur et peut donc contenir des caractères en plus de la ville « from Paris »…

Voici la requête que j'ai réalisée:

Code:

---

1 2 3 4

SELECT commune.NCCENR, COUNT(*) FROM `BASE`.`refcommune` as commune left join `BASE`.`utilisateur` as user on user.Localisation like commune.NCCENR GROUP BY NCCENR;

---

Mais elle me retourne 1 pour toutes les villes

Je travail sur MySQL Workbench 5.2.47

Merci.

Bonjour,

Code:

---

1 2 3 4

SELECT commune.NCCENR, COUNT(user.nom) FROM `BASE`.`refcommune` AS commune LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE commune.NCCENR GROUP BY NCCENR;

---

Il faut préciser la table user pour le count.

Merci pour ta réponse Exia93,
Mais cela ne marche toujours pas, j'obtiens maintenant 0 pour chaques villes

Bonjour,

Le LIKE tel que vous le faites revient à un =...

essayez ceci :

Code:

---

1 2 3 4 5

SELECT commune.NCCENR, COUNT(user.nom) FROM `BASE`.`refcommune` AS commune LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE '%' + commune.NCCENR + '%' GROUP BY NCCENR;

---

La colonne Localisation de la table user est-elle bien une clé étrangère vers la colonne NCCENR de la table Commune ?

Citation:

---

Envoyé par aieeeuuuuu

Bonjour,

Le LIKE tel que vous le faites revient à un =...

essayez ceci :

Code:

---

1 2 3 4 5

SELECT commune.NCCENR, COUNT(user.nom) FROM `BASE`.`refcommune` AS commune LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE '%' + commune.NCCENR + '%' GROUP BY NCCENR;

---

---

J'ai essayé ta méthode mais cela ne marche pas ,j'obtiens 0 pour chaques villes

Citation:

---

Envoyé par Exia93

La colonne Localisation de la table user est-elle bien une clé étrangère vers la colonne NCCENR de la table Commune ?

---

Je ne peux pas déclarer Localisation comme clé étrangère de NCCENR car Localisation n'est pas une clé primaire

pardon, mauvaise concaténation de ma part pour MySQL:

c'est plutot ceci :

Code:

---

1 2 3 4 5

SELECT commune.NCCENR, COUNT(user.nom) FROM `BASE`.`refcommune` AS commune LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE CONCAT('%', commune.NCCENR , '%') GROUP BY NCCENR;

---

salut

je ne crois pas que le + concatène, il transforme plutôt le résultat en nombre...
utilise plutôt:

Code:

---

concat('%',commune.NCCENR,'%')

---

dans le like

Citation:

---

Envoyé par ericd69

salut

je ne crois pas que le + concatène, il transforme plutôt le résultat en nombre...
utilise plutôt:

Code:

---

concat('%',commune.NCCENR,'%')

---

dans le like

---

oui c'est ce que j'avais utilisé pour tester la méthode de aieeeuuuuu et le résultat reste le même...

il faudrait fournir un jeu d'essai. La requête me semble bonne !

deux questions tout de même :

- est-ce que les noms sont bien renseignés dans voter base ? ou est-ce qu'ils sont à NULL ?
- quelle est la collation des colonnes user.Localisation et commune.NCCENR ?

Oui les noms sont tous renseignés.Je ne comprends pas ce que tu entends par collation entre les colonnes.

Je mets en PJ le code et les fichiers de jeu de données, peut etre que cela aidera.

Merci pour votre aide.

Chez moi j'obtient bien le résulat voulu seulement vue qu'il y'a 36000 communes et que 13 utilisateurs tu as beaucoup de commune avec 0 utilisateur.

Pour voir si la jointure ce fait bien il suffit de faire la vérification en affichant seulement les commune avec plus d'1 utilisateur:

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT commune.NCCENR, COUNT(user.nom) FROM `refcommune` AS commune LEFT JOIN `utilisateur` AS user ON user.Localisation = commune.NCCENR GROUP BY NCCENR HAVING COUNT(user.nom) > 0   -- Ou plus logiquement en utilisant une jointure interne SELECT commune.NCCENR, COUNT(user.nom) FROM `refcommune` AS commune INNER JOIN `utilisateur` AS user ON user.Localisation = commune.NCCENR GROUP BY NCCENR

---

De plus j'utilise l'opérateur = au lieu de like vue que je ne fait pas de traitement sur la chaîne de caractère.

J'ai appliqué ces commandes pour nettoyer les sauts de ligne sur les colonnes Localisation et NCCENR

Code:

---

1 2 3 4 5 6 7 8

update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\r',''); update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\n',''); update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\r\n','');   update `utilisateur` set `Localisation` = replace(`Localisation` ,'\r',''); update `utilisateur` set `Localisation` = replace(`Localisation` ,'\n',''); update `utilisateur` set `Localisation` = replace(`Localisation` ,'\r\n','');

---

Changer le COUNT(*) en COUNT(user.nom) supprime les 1 retournés pour chaque ville.
La requête suivante fonctionne bien (environ 3 secondes) :

Code:

---

1 2 3 4 5 6

SELECT c.NCCENR, COUNT(r.nom) total FROM refcommune c LEFT JOIN utilisateur r ON r.Localisation = c.NCCENR GROUP BY c.NCCENR ORDER BY total DESC

---

J'ai aussi ajouté des index sur les champs NCCENR et Localisation et cela m'a permis de diviser le temps d'exécution en 10. Il sera difficile d'optimiser d'une telle requête. Les meilleures performances sont obtenues avec la requête ci-dessous (environ 0.008 secondes) :

Code:

---

1 2 3 4 5 6

SELECT c.NCCENR, COUNT(r.nom) total FROM refcommune c, utilisateur r WHERE r.Localisation = c.NCCENR GROUP BY c.NCCENR ORDER BY total DESC

---

Les deux dernières requêtes que tu donne sont complètement différente la première est une jointure externe tandis que la dernière une interne seulement elle est écrite comme il y'a 20 ans :

Code:

---

1 2 3 4 5 6

-- Ecriture actuelle et plus lisible je trouve SELECT c.NCCENR, COUNT(r.nom) total FROM refcommune c INNER JOIN utilisateur r ON r.Localisation = c.NCCENR GROUP BY c.NCCENR ORDER BY total DESC

---

Merci pour votre aide.
D’après ce que vous me dite le problème doit être résolu, cependant en prenant vos solutions j'obtiens des colonnes vides, le problème doit donc venir d'ailleurs.

Si c'est lorsque vous utilisez la jointure externe c'est qu'il ne doit pas y avoir d'enregistrement dans la table utilisateur pour une commune et donc 0 est retourné.

Citation:

---

Envoyé par Exia93

Si c'est lorsque vous utilisez la jointure externe c'est qu'il ne doit pas y avoir d'enregistrement dans la table utilisateur pour une commune et donc 0 est retourné.

---

non il retourne rien c'est a dire je n'ai même pas la liste des villes affichée.

Si tu n'a aucun résultat c'est que la clause de jointure ON r.Localisation = c.NCCENR n'est pas valide, que contiennent exactement c'est deux colonnes ?

Citation:

---

Envoyé par Exia93

Si tu n'a aucun résultat c'est que la clause de jointure ON r.Localisation = c.NCCENR n'est pas valide, que contiennent exactement c'est deux colonnes ?

---

Localisation contient le nom de ville renseigné par l'utilisateur et NCCENR contient la liste de toutes les villes de france. J'ai joints les fichiers dans un des messages au dessus.