CODEWITHSUNDEEP
phpmysql
Koibu
Koibu

Reputation: 31

Count number of rows and number of value matches

SELECT COUNT(m.winner = p.userName) AS wins, YEAR(m.datePlayed) AS year, DATE_FORMAT(m.datePlayed, '%M') AS month, COUNT(*) as games FROM allMatches AS m LEFT JOIN playerUsers AS p ON m.season = p.season AND (m.player1 = p.userName OR m.player2 = p.userName) WHERE p.realName = '$realName' AND m.winner != '' AND m.canceled != '1' GROUP BY YEAR(m.datePlayed), MONTH(m.datePlayed)";

We have 2 tables we're joining: allMatches and playerUsers. A PlayerUser has a username which matches to either player1 or player2 (and possibly to winner) on the allMatches table for a given season.

I want to count the number of games played (any match they're in) and the number of games they've won (where their username on playerUsers matches winner on allMatches). The above SQL statement returns both wins and games as the same number. Is there a way to count the number of wins and games in the same SQL statement?

Upvotes: 1

Views: 81

Answers (1)

Mark Byers
Mark Byers

Reputation: 838096

Use SUM instead of COUNT to calculate the number of wins:

SELECT SUM(m.winner = p.userName) AS wins, ...

It works because the boolean expression evaluates to either 0 and 1, and so SUM gives you the total number of rows where the expression was true.

Upvotes: 2

Related Questions