Mysql: using case condition when selecting variable list from table

Javi

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.

Chris Lear

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

selecting duplicate values with a condition from a mysql table

From Dev

PHP Variable Error When Selecting From Table

From Dev

Error when selecting colum names from table in MySQL information schema

From Dev

selecting unique table on each condition with mysql

From Dev

selecting unique table on each condition with mysql

From Dev

MySQL selecting rows based on condition of additional table

From Dev

Error "Unknown system variable 'lower_case_table_names'" when accessing MySql from Eclipse

From Dev

Selecting all values from a table in mysql using a stored procedure

From Dev

Using a variable as a case condition in zsh

From Dev

MySQL Case When with 2 Condition

From Dev

MySQL Case When with 2 Condition

From Dev

Selecting items matching a specific condition from a list

From Dev

Mysql WHERE using AND & CASE Condition

From Dev

Using case when mysql

From Dev

Selecting hierarchical data using MySQL variable

From Dev

Selecting hierarchical data using MySQL variable

From Dev

R selecting a table from a list of tables by id

From Dev

data table or list in selecting data from db

From Dev

Selecting MAX number from MySQL table not working

From Dev

mySQL - Selecting the latest row from a table

From Dev

Selecting a Diff report from one table MySQL

From Dev

php selecting an order from a different mysql table

From Dev

Selecting MAX number from MySQL table not working

From Dev

selecting weighted random distribution from a mysql table

From Dev

MySQL - selecting random row from large table

From Dev

Displaying field data of mysql table with selecting field name from the drop down list

From Dev

Using a Count condition in a Case statement to classify a variable

From Dev

MySQL if statement or case when with where condition

From Dev

Another MySQL 1066. Not unique table/alias: 'events' when selecting from multiple tables

Related Related

  1. 1

    selecting duplicate values with a condition from a mysql table

  2. 2

    PHP Variable Error When Selecting From Table

  3. 3

    Error when selecting colum names from table in MySQL information schema

  4. 4

    selecting unique table on each condition with mysql

  5. 5

    selecting unique table on each condition with mysql

  6. 6

    MySQL selecting rows based on condition of additional table

  7. 7

    Error "Unknown system variable 'lower_case_table_names'" when accessing MySql from Eclipse

  8. 8

    Selecting all values from a table in mysql using a stored procedure

  9. 9

    Using a variable as a case condition in zsh

  10. 10

    MySQL Case When with 2 Condition

  11. 11

    MySQL Case When with 2 Condition

  12. 12

    Selecting items matching a specific condition from a list

  13. 13

    Mysql WHERE using AND & CASE Condition

  14. 14

    Using case when mysql

  15. 15

    Selecting hierarchical data using MySQL variable

  16. 16

    Selecting hierarchical data using MySQL variable

  17. 17

    R selecting a table from a list of tables by id

  18. 18

    data table or list in selecting data from db

  19. 19

    Selecting MAX number from MySQL table not working

  20. 20

    mySQL - Selecting the latest row from a table

  21. 21

    Selecting a Diff report from one table MySQL

  22. 22

    php selecting an order from a different mysql table

  23. 23

    Selecting MAX number from MySQL table not working

  24. 24

    selecting weighted random distribution from a mysql table

  25. 25

    MySQL - selecting random row from large table

  26. 26

    Displaying field data of mysql table with selecting field name from the drop down list

  27. 27

    Using a Count condition in a Case statement to classify a variable

  28. 28

    MySQL if statement or case when with where condition

  29. 29

    Another MySQL 1066. Not unique table/alias: 'events' when selecting from multiple tables

HotTag

Archive