[MySQL] Jointure et Sum

Je fait une requete :

Code:

---

SELECT id, pseudo, team, xp_spatiale, xp_terre FROM players WHERE active != 0

---

jusqu'a la pas bien compliquer ...

ensuite j'aimerai que le resultat me retroune en meme temps le nombre de planete que posede un joueur

Code:

---

SELECT SUM(planetes) WHERE planetes.players = players.id

---

ou un truc dans ce genre la :? mais je sais pas faire :oops:

pouvez vous m'aidez :?

Salut,

Est-ce que ceci te convient ?

Code:

---

1 2 3 4

SELECT id, pseudo, team, xp_spatiale, xp_terre, sum(planete.*) FROM players INNER JOIN planetes ON players.id = planetes.players WHERE active != 0 GROUP BY id, pseudo, team, xp_spatiale, xp_terre

---

[Edit] Ca se rapproche un peu de ça :
http://www.developpez.net/forums/vie...34720&start=33
(dans ce topic, il y a une jointure externe, utile au cas où un joueur n'a pas de planètes)
[/Edit]

Citation:

---

MySQL a répondu:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM players INNER JOIN planetes ON players . id = planetes . players WHER' at line 1

---

Code:

---

1 2 3 4

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, count(planetes.players) FROM players INNER JOIN planetes ON players.id = planetes.players WHERE active != 0 GROUP BY id, pseudo, team, xp_spatiale, xp_terre

---

voila :lol:
par contre

Code:

---

1 2 3 4 5

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, COUNT(planetes.players) FROM players INNER JOIN planetes ON players.id = planetes.players WHERE active != 0 GROUP BY players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre ORDER BY.COUNT(planetes.players)

---

ça fonctionne pas pk ? :cry:

Citation:

---

Envoyé par dark_vidor

par contre

Code:

---

1 2 3 4 5

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, COUNT(planetes.players) FROM players INNER JOIN planetes ON players.id = planetes.players WHERE active != 0 GROUP BY players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre ORDER BY.COUNT(planetes.players)

---

ça fonctionne pas pk ? :cry:

---

Quel est le message d'erreur ? Pour moi, la syntaxe est correcte (testée sous Oracle), sauf le "." après le ORDER BY, sans doute due à une erreur de copie ?

Citation:

---

Erreur
requête SQL:

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, COUNT( planetes.players )
FROM players
INNER JOIN planetes ON players.id = planetes.players
WHERE active !=0
GROUP BY players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre
ORDER BY.COUNT(

planetes.players
)
LIMIT 0 , 30
MySQL a répondu:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( planetes . players ) LIMIT 0, 30' at line 1

---

Citation:

---

Envoyé par dark_vidor

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, COUNT( planetes.players )
FROM players
INNER JOIN planetes ON players.id = planetes.players
WHERE active !=0
GROUP BY players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre
ORDER BY.COUNT(planetes.players)

---

Tu as un POINT (souligné en rouge ci-dessus) entre ORDER BY et COUNT, est-ce que ça fonctionne mieux si tu l'enlèves ?

Citation:

---

Erreur
requête SQL:

SELECT players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre, COUNT( planetes.players )
FROM players
INNER JOIN planetes ON players.id = planetes.players
WHERE active !=0
GROUP BY players.id, players.pseudo, players.team, players.xp_spatiale, players.xp_terre
ORDER BY COUNT( planetes.players )
LIMIT 0 , 30
MySQL a répondu:

#1111 - Invalid use of group function

---