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

Question

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.

Answer 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

• OmniOwl: Thank you so much! That is a bit more involved than I'd have thought, but not so bad! I really need to polish my SQL skills back up again after doing mostly backend code for so long :D
• nbk: Mysql 8 and Maradb 10.2 have window functions, that you must look at, they make the life in combination with cTE easier
• OmniOwl: Will try and keep that in mind. Thanks for the tip.