Using COUNT() implicitly for making a view over game match data? 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.
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 &lt;&gt; 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&lt;&gt;fiddle here