Using COUNT() implicitly for making a view over game match data?

All we need is an easy explanation of the problem, so here it is.

I’ve not used SQL in quite a while and I’m pretty rusty it seems. I’ve had to do a project recently and needed to use MySQL on Windows and when looking for solutions to this particular query, I get told that you can use COUNT(*) implicitly somehow, but I can’t get it to work right.

What I need is something like:

PLAYER_ID MATCHES_WON MATCHES_LOST
0 4 1
1 2 5
2 2 2

I have made a simple SQL fiddle of my database here.

Could someone help me out with this? I’m willing to accept that I might have to make a more involved solution with some CURSOR and LOOP but thought I’d ask here. It’s my first question on this community, so sorry if I didn’t format this right or it wasn’t an appropriate question.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This select first all individual player_IDs and uses them to get the numbers

CREATE TABLE `game_match` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the match,',
  `ACCOUNT_ONE_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 1.',
  `ACCOUNT_TWO_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 2.',
  `WINNER_ID` INT(10) NOT NULL COMMENT 'Foreign key for which of the two players won.',
  INDEX(ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID) ,
  PRIMARY KEY (`ID`) USING BTREE
);

INSERT INTO game_match
  (ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID)
VALUES
  (0, 1, 0),
    (1, 2, 2),
    (1, 2, 1),
    (0, 1, 0),
    (1, 0, 0),
    (2, 1, 2),
    (0, 2, 0),
    (0, 1, 1);
SELECT
DISTINCT
PlayerID,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID =  t1.PlayerID) MATCHES_WON,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID <> t1.PlayerID) MATCHES_LOST
FROM (SELECT DISTINCT ACCOUNT_ONE_ID  AS PlayerID FROM game_match
UNION
SELECT DISTINCT ACCOUNT_TWO_ID FROM game_match) t1
PlayerID | MATCHES_WON | MATCHES_LOST
-------: | ----------: | -----------:
       0 |           4 |            1
       1 |           2 |            5
       2 |           2 |            2

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply