I have a large table and need to select some variables. First, this is the table structure:
gamecode---team---player---won
1---TeamA---TeamAPlayer1---1
1---TeamA---TeamAPlayer2---1
1---TeamA---TeamAPlayer3---1
1---TeamB---TeamBPlayer1---0
1---TeamB---TeamBPlayer2---0
2---TeamA---TeamAPlayer1---0
2---TeamA---TeamAPlayer2---0
2---TeamB---TeamBPlayer1---1
3---TeamC---TeamCPlayer1---0
3---TeamD---TeamDPlayer1---1
3---TeamD---TeamDPlayer2---1
4---TeamB---TeamBPlayer1---1
4---TeamD---TeamDPlayer1---0
That is, Won is a dummy variable that takes 1 if that team won the game, and 0 otherwise.
And now, my purpose: I want to count, grouping by team or by player the number of victories. I don't want to include that in the WHERE clause, but in the SELECT part, as the number of victories is only one among many others I want to collect with the same SELECT.
I tried this:
SELECT count(distinct gamecode*won) as victories FROM Mytable WHERE <conditions>
But with that select a team that lost all the games will count for 1 victory, since all gamecodewon will equal zero. And all teams winning and loosing some games will count one more victory that they have, as all games lost will result on a 'zero' value for gamecodewon variable.
What about including CASE in the SELECT, something like:
SELECT count(distinct case when gamecode*won is not 0 then 1 else 0 end) FROM Mytable WHERE <conditions>
Of course this does not work. Any suggestions? Thanks to everybody.
The expected result set would be, in case I group by team:
team---victories
TeamA---1
TeamB---2
TeamC---0
TeamD---1
That is, TeamA won only gamecode 1 and lost gamecode 2; TeamB won gamecode 2 and gamecode 4, while lost gamecode 1; TeamC won no game and lost gamecode 3; finally, TeamD won gamecode 3 and lost gamecode 4.
http://sqlfiddle.com/#!9/3a6a3/2
This fiddle shows one way to do it.
The query is
select count(distinct (nullif(gamecode*won,0))) as victories, team from games group by team;
See http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_nullif
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments