0

I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table:

id result dice1 dice2 player_id
1 LOSE 4 4 9
2 LOSE 4 5 7
3 WIN 4 3 9
4 LOSE 2 3 9
5 WIN 6 1 7
6 WIN 1 6 7
7 LOSE 1 3 7

I need to display the percentage of win of each player and this is the query right now but it's not working, it gives me back the same percentage for every player:

SELECT DISTINCT player_id,
ROUND(((SELECT COUNT(id_game) FROM game WHERE result = 'WIN' ) / 
(SELECT COUNT(id_game) FROM game )) * 100 ) AS percentage
FROM game
group by player_id

Do you think it's even possible to do that? I'm starting to doubt it

1
  • Hi, and welcome to the forum! Could you please (and with all questions) include your version of MySQL? - Tag the question! – Vérace Apr 7 at 8:39
0
SELECT player_id,
       COUNT(result) total,
       ROUND(100 * SUM(result = 'WIN') / COUNT(result)) winning_percent,
       ROUND(100 * SUM(result = 'LOSE') / COUNT(result)) loosing_percent
FROM game
GROUP BY player_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a04d576b918c392f90c1d835fd1a6f51

0

What I did was the following (all the code below is available here - an alternative solution is available here):

Solution (question as asked):

CREATE TABLE score
(
  id INTEGER             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  result CHAR(1)         NOT NULL,
  die_1 TINYINT UNSIGNED NOT NULL,
  die_2 TINYINT UNSIGNED NOT NULL,
  player_id INTEGER      NOT NULL,
  
  CONSTRAINT result_w_l_ck  CHECK (result IN ('W', 'L')),
  CONSTRAINT die_1_throw_ck CHECK (die_1 BETWEEN 1 AND 6),
  CONSTRAINT die_2_throw_ck CHECK (die_2 BETWEEN 1 AND 6)
  
);

Note the NOT NULL requirements and also the other constraints - not only do they serve to protect the integrity of your data (your db is your last line of defence against garbage input!), it also helps the optimiser to choose the best plan. Note that the CHECK constraints will not be enforced before MySQL version >= 8.0.16 - incredibly, versions before that "parse" them but don't enforce them - ya gotta love MySQL!

populate the table:

INSERT INTO score VALUES
(1, 'L',    4,  4,  9),
(2, 'L',    4,  5,  7),
(3, 'W',    4,  3,  9),
(4, 'L',    2,  3,  9),
(5, 'W',    6,  1,  7),
(6, 'W',    1,  6,  7),
(7, 'L',    1,  3,  7);

And then I built up (note built up) the following query:

SELECT 
  player_id,
  SUM(CASE 
    WHEN result = 'W' THEN 1
    ELSE 0
  END) AS wins,
  SUM(CASE 
    WHEN result = 'L' THEN 1
    ELSE 0
  END) AS losses,
  COUNT(player_id) AS cnt,
  (SUM(CASE 
    WHEN result = 'W' THEN 1
    ELSE 0
  END))/(COUNT(player_id)) AS w_proportion,
  
  ROUND((SUM(CASE 
    WHEN result = 'W' THEN 1
    ELSE 0
  END))/(COUNT(player_id)) * 100, 2) AS w_percent  
  
FROM score
GROUP BY player_id
ORDER BY player_id;

Result:

player_id   wins    losses  cnt w_proportion    w_percent
        7      2         2    4       0.5000        50.00
        9      1         2    3       0.3333        33.33

So, as you move down the query (and across the results), you can follow the "trail" of how the final result was finally arrived at.

Be very careful here - MySQL does INTEGER division differently to other systems - the SQL standard mandates that 5/2 = 2 - see bottom of the PostgreSQL1 fiddle here - also, check out how the final result is obtained by using casting (the ::<data_type> is PostgreSQL shorthand for the SQL standard CAST (variable AS data_type>).

Also, MySQL uses another (!) non-standard "trick" to allow summing of Booleans - as you can see from the PostgreSQL fiddle, this won't work on other servers - again, one to be careful of!

1 PostgreSQL is just about the most standards compatible RDBMS out there - if you run the code using some of the other servers, you will see that it fails on those also.

Alternative solution (fiddle):

Since the result is dependent on the score of both throws, we don't really need that column - as follows:

CREATE TABLE score -- Note, no result field!
(
  id INTEGER             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  die_1 TINYINT UNSIGNED NOT NULL,
  die_2 TINYINT UNSIGNED NOT NULL,
  player_id INTEGER      NOT NULL,
  
  CONSTRAINT die_1_throw_ck CHECK (die_1 BETWEEN 1 AND 6),
  CONSTRAINT die_2_throw_ck CHECK (die_2 BETWEEN 1 AND 6),
  CONSTRAINT d1_p_d2_lt_13_ck CHECK ((die_1 + die_2) < 13)
  
);

Populate:

INSERT INTO score VALUES
(1, 4,  4,  9),
(2, 4,  5,  7),
(3, 4,  3,  9),
(4, 2,  3,  9),
(5, 6,  1,  7),
(6, 1,  6,  7),
(7, 1,  3,  7);

And the SQL:

SELECT 
  player_id,

  SUM(CASE 
    WHEN die_1 + die_2 = 7 THEN 1
    ELSE 0
  END) AS wins,

  COUNT(player_id) - 
  SUM(CASE 
    WHEN die_1 + die_2 = 7 THEN 1
    ELSE 0
  END) AS losses,
  
  SUM(CASE 
    WHEN die_1 + die_2 = 7 THEN 1
    ELSE 0
  END)/COUNT(player_id) AS prop_w,

  ROUND(SUM(CASE 
    WHEN die_1 + die_2 = 7 THEN 1
    ELSE 0
  END)/COUNT(player_id) * 100, 2) AS percent_w
 
FROM score
GROUP BY player_id
ORDER BY player_id;

Result (same):

player_id   wins    losses    prop_w    percent_w
        7      2         2    0.5000        50.00
        9      1         2    0.3333        33.33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.