CODEWITHSUNDEEP
mysql
Clain29
Clain29

Reputation: 80

Mysql: Count wins but only once per opponent

I'm looking for help using sum() in my SQL query:

Task: Count tournament wins of all players. (one number per player) (battles.result = 1 means Player1 wins)

SELECT members.id, members.name,
            (
                SELECT SUM(battles.result = 1)
                FROM battles
                WHERE members.id = battles.player1 AND battles.result=1 order by battles.gametime
            (   as wins,
FROM members

Next: Only count ONE result per two players.

So if there are multiple results of two players, count only the first result (first gametime).

I've already tried using order by battles.player2, but i guess there is a much better solution?

Upvotes: 0

Views: 58

Answers (1)

cdaiga
cdaiga

Reputation: 4939

You can easily get the result by doing a join and aggregation instead. Try:

SELECT A.id, A.name, SUM(IFNULL(B.result,0)) wons
FROM members A LEFT JOIN battles B
ON A.id=B.player1
GROUP BY  A.id, A.name;

Upvotes: 1

Related Questions