Requete de classement SQL sur trois tables avec SUM, COUNT..

[nickargall]

Bonsoir à tous
Je suis pas fortiche en SQL et j'ai besoin de vos lumières.

J'ai, dans une table PRONO, les champs suivant qui m'intéressent :
| points | id_user |
5 1
5 1
10 2
50 2

Dans une table USER, les champs suivant :
id_user | pseudo
1 POPOL
2 LULU

Et une troisième table MESSAGES qui recense les messages postés sur mon forum par les membres :
j'y ai notamment les champs suivants :
id_message | nom ..
1 LULU
2 LULU
3 POPOL
4 LULU

Dans un premier temps, j'ai voulu sortir le classement des [user.nom] en fonction du total des [prono.points]. J'ai donc mis en place une requête SQL :

SELECT SUM(points), prono.id_user, user.pseudo FROM prono,user WHERE user.id_user=prono.id_user GROUP BY id_user ORDER BY SUM(points) DESC

et ca fonctionne très bien.

Maintenant la complication :

Je voudrais pouvoir également classer les [user.nom] en fonction du total des [prono.points] descendant ET du nombre total de [messages.id_message] qui lui correspondent.

J'ai donc "bêtement" tenté de rajouter un COUNT(messages.id_message) d'un coté et un WHERE messages.nom=user.pseudo de l'autre :

SELECT SUM(points), COUNT(messages.id_message), prono.id_user, user.pseudo FROM prono,user, messages  WHERE messages.nom=user.nom AND user.id_user=prono.id_user GROUP BY id_user ORDER BY SUM(points) DESC

Mais bon ca le fait pas du tout. Mon total de SUM(points) correspond pas du tout à ce que je voudrais, ni mon COUNT(id_message) non plus d'ailleurs.

D'ou ma question : avec les données indiquées ci dessus, comment obtenir un classement des user.pseudo selon la somme de points Descendante et le nombre de id_messages descendant ?

Merci à celui qui me dira : "C'est pas compliqué, essaie plutôt comme ça "

 

[YoyoS]

Salut, t'es sure que dans ta table MESSAGES il y a des noms et pas des id_user ?

 

[nickargall]

Ouaip, une coquille du mec qui a créé la base de données à l'origine

bon ok c'est moi, je l'avoue

 

[YoyoS]

Si je pouvais avoir le code exporté de la structure de tes tables et quelques données je me donnerais à cœur joie pour tester

La j'ai pu faire que ça mais il y a de grandes chances que ça ne marche pas

SELECT SUM(p.points) as pts,COUNT(m.*) as nbMess,p.id_user, u.nom
FROM prono p,user u,messages m
WHERE u.id_user = p.id_user AND u.nom = m.nom
GROUP BY p.id_user,u.nom
ORDER BY pts DESC,nbMess DESC;

Pas trop envie de réfléchir, donc si je pouvais tester :mrgreen:

 

[nickargall]

merci de l'essai ... non transformé Après quelques corrections, j'obtiens les mêmes résultats que la requête que j'ai tenté.

SELECT SUM( p.points ) AS pts, COUNT( m.id_message ) AS nbMess, p.id_user, u.pseudo
FROM prono p, user u, messages m
WHERE u.id_user = p.id_user
AND u.pseudo = m.nom
GROUP BY p.id_user, u.nom
ORDER BY pts DESC , nbMess DESC

mais merci d'avoir essayé

 

[oxado]

Quelque chose comme:

select sum_points,count_messages,id_user,user_pseudo from (SELECT SUM(points) as sum_points, prono.id_user, user.pseudo AS user_pseudo FROM prono,user WHERE user.id_user=prono.id_user GROUP BY id_user) s1,(SELECT messages.nom,count(*), prono.id_user FROM messages GROUP BY nom) s2 WHERE user_pseudo=nom

non?

Jacques.

 

[YoyoS]

Moi j'ai envie de tester en ligne, pas de jouer aux devinettes

 

[nickargall]

@jacques :
MySQL a répondu:
#1054 - Unknown column 'prono.id_user' in 'field list'

@Yoyos : ma structure de table t'arrive par email

Merci de vos tentatives à tous les deux

 

[nickargall]

oui mais non en fait j'ai rien dit, yoyos a supprimé un message

 

[YoyoS]

Teste moi ça, c'est dlabonne ;o

SELECT SUM(p.points) as pts, p.id_user, u.pseudo,
  (SELECT COUNT(*) 
   FROM messages m 
   WHERE m.nom = u.nom) as nbMess
FROM prono p,user u 
WHERE u.id_user = p.id_user
GROUP BY u.id_user
ORDER BY pts DESC,nbMess DESC

A oui, j'avais supprimé ma réponse, je me suis vite rendu compte que ce n'était pas les bons résultats

 

[nickargall]

Teste moi ça, c'est dlabonne ;o

SELECT SUM(p.points) as pts, p.id_user, u.pseudo,
  (SELECT COUNT(*) 
   FROM messages m 
   WHERE m.nom = u.nom) as nbMess
FROM prono p,user u 
WHERE u.id_user = p.id_user
GROUP BY u.id_user
ORDER BY pts DESC,nbMess DESC

A oui, j'avais supprimé ma réponse, je me suis vite rendu compte que ce n'était pas les bons résultats

Aaaaah merci Voilà une requete qu'elle est bonne Impeccable

J'aurais appris qu'on peut faire ça :

...  (SELECT COUNT(*)
   FROM messages m
   WHERE m.nom = u.nom) as nbMess ...

J'me coucherai moins bête ce soir Merci a tous et spéciale dédicace à Yoyos (comme disent les djeunes des forums)

 

[YoyoS]

Mais 2 rien, ça fera un BL mon cher :mrgreen: Oue on est pas encore taxé sur le bl j'en profite

 

[nickargall]

Tu crois qu'un BL émanant d'un site sur le foot aidera ton positionnement sur la thématique des tutoriels totoshop ?

 

[YoyoS]

Ce qui est sure c'est que ça ne peut pas le faire plonger Attend, je vais faire un tuto pour créer un ballon de foot :mrgreen:

 

[nickargall]

Euh en fait ca marche pas a 100%. Il me donne 0 pour le nbMess de id_ser qui devraient en avoir.Fichtre.
Edit : Bon j'ai trouvé l'"erreur" qui se glissait dans la requete que tu proposais. c'était juste au niveau de la requete imbriquée :

(SELECT COUNT(*)
FROM messages m
WHERE m.nom = u.pseudo) as nbMess

au lieu de

(SELECT COUNT(*)
FROM messages m
WHERE m.nom = u.nom) as nbMess

 

[YoyoS]

Fais quelques requête pour changer la clé étrangère en id_user, ça sera plus claire.

Si d'autres soucis hésite pas !

 

[nickargall]

mouais, mais il me faudra changer aussi plein de PHP dans ma bibliothèque de fonction Pour l'instant on va laisser comme ça, je verrai ça pour ma prochaine remise à jour . Mais c'est clair que ça serait plus simple