MySQL: Mehrere Counts in einem Query

[sensei17]

Hallo,

ich habe ein Problem bezüglich einer MySQL-Abfrage, die ich nicht hinkriege. Ich hoffe jemand kann mir helfen. Ich komme leider auch nicht zu einem Ansatz in die richtige Richtung.

Ich beziehe mich auf zwei Tabellen:

users:
id | name

battles:
userid1 | userid2 | winner

Es geht darum, dass die Mitglieder des Forums in kleinen 1-gegen-1-Contests gegeneinander antreten, wobei einer von beiden immer als Gewinner speichert wird.
Die Felder userid1, userid2 und winner speichern jeweils die UserIDs der beiden teilnehmenden User ab, folglich ist "winner" immer gleich "userid1" oder "userid2".

So, nun habe ich versucht eine Abfrage zu konstruieren, in der die User ihrer Gewinnquote nach sortiert werden, d.h. nach dem Quotient aus "Anzahl der gewonnen battles" durch "Anzahl der insgesamt gespielten battles".

der erste Teil war noch recht leicht:

SELECT
	battles.winner as userid,
	users.name as username,
	COUNT(*) as count_wins
FROM
	battles,
	users
WHERE
	battles.winner=users.id
GROUP BY
	battles.winner
ORDER BY
	count_wins DESC;

Mit dieser Abfrage werden also die User (mit ID und Name) nach ihrer absoluten Gewinnanzahl sortiert und ausgegeben, aber leider berücksichtigt sie nicht die Anzahl der teilgenommenen Spiele.

Die Ausgabe sieht etwa so aus:

[0] => Array
	(
		[userid] => 10
		[username] => user10
		[count_wins] => 4
	)

[1] => Array
	(
		[userid] => 13
		[username] => user13
		[count_wins] => 3
	)

[2] => Array
	(
		[userid] => 12
		[username] => user12
		[count_wins] => 3
	)

[3] => Array
	(
		[userid] => 11
		[username] => user11
		[count_wins] => 2
	)

Wenn ich ein zweites Count einbaue, erhält dies leider den gleichen Wert wie das schon eingebaute COUNT(). Das GROUP BY scheint da das ganze zu beschränken, aber wenn ichs wegnehme werde die 'winners' nicht mehr gezählt.

Ich suche also etwas, das so in der Art anfängt:

SELECT
	battles.winner as userid,
	users.name as username,
	COUNT(xxx) as count_wins,
	COUNT(yyy) as count_games,
	count_wins/count_games as winning_rate
FROM
	battles,
	users
.............................

Kann mir bitte jemand mit meinem Problem weiterhelfen?
Ich hab schon was mit UNION versucht, um zwei SELECTs zu verknüpfen, aber das hat auch nicht funktioniert, ich bastel schon seit vielen Stunden rum, aber mir fällt nichts mehr ein.

Wär echt sehr nett, wenn jemand ne Idee hat, um mir weiterzuhelfen

Schonmal vielen Dank im Voraus =)

 

[hpvw]

Du könntest mal mit diesem ungetesteten Query versuchen:

select
  users.name,
  count(battles.winner) as gekaempfteBattles,
  sum(users.id=battles.winner) as gewonneneBattles,
  sum(users.id=battles.winner)/count(battles.winner) as AnteilGewonnen
from users
left join battles
  on users.id=battles.userid1 or users.id=battles.userid2
group by users.id
order by AnteilGewonnen desc

Gruß hpvw

 

[sensei17]

Dieses Query funktioniert perfekt, vielen Dank!!

Jetzt noch eine kleine Frage dazu:

Wie kann ich z.B. über eine WHERE-Klausel die Ausgabe begrenzen, dass z.B. nur User ausgegeben werden, bei denen gekaempfteBattles >= 5 ist?

ich habs mit sowas versucht...

SELECT
	users.id,
	users.name,
	count(battles.winner) AS gekaempfteBattles,
	sum(users.id=battles.winner) AS gewonneneBattles,
	sum(users.id=battles.winner)/count(battles.winner) AS AnteilGewonnen
FROM
	users
LEFT JOIN
	battles
ON
	users.id=battles.userid1
	OR
	users.id=battles.userid2
GROUP BY
	users.id
ORDER BY
	AnteilGewonnen DESC
WHERE
	gekaempfteBattles >=1
LIMIT
	20;

... aber dabei tritt ein Syntax-Error auf.

Gibt es da eine Möglichkeit, das Ganze zu begrenzen?

Aber erst schonmal vielen Dank, hpvw, hat mir echt weitergeholfen!!

 

[sensei17]

Ich nehm die Frage zurück
Hat sich gerade erledigt:

SELECT
	users.id,
	users.name,
	count(battles.winner) AS gekaempfteBattles,
	sum(users.id=battles.winner) AS gewonneneBattles,
	sum(users.id=battles.winner)/count(battles.winner) AS AnteilGewonnen
FROM
	users
LEFT JOIN
	battles
ON
	users.id=battles.userid1
	OR
	users.id=battles.userid2
GROUP BY
	users.id
HAVING
	gekaempfteBattles >= 5
ORDER BY
	AnteilGewonnen DESC
LIMIT
	20;

Aber merci für den ersten Query!