Create two aggregated virtual tables from one table and join-CodePudding

I want to build a query based one table. From this one table I want to create two virtual tables based on an count aggregation query. In my example it would split to original table into a table where Xbox Games per Player are counted and one where Playstation Games per player are counted. Then the results of the queries are joined based on the PlayerID.

create table data (PlayerID text, game text, platform text);

insert into data (PlayerID, game, platform) values
('Player1', 'Fifa', 'Playstation'),
('Player1', 'Tekken', 'Playstation'),
('Player1', 'Gears of War', 'Xbox'),
('Player1', 'Ninja Gaiden', 'Playstation'),
('Player2', 'Gears of War', 'Xbox'),
('Player1', 'Metal Slug Anthology', 'Playstation'),
('Player1', 'Metal Gear V', 'Playstation'),
('Player2', 'Halo', 'Xbox'),
('Player3', 'Street Fighter', 'Playstation'),
('Player3', 'Madden NFL', 'Playstation'),
('Player1', 'Final Fantasy', 'Xbox'),
('Player2', 'Ratchet & Clank', 'Playstation');

The result should look like this:

| PlayerID | playedPlaystationGames | playedXBoxGames |
|----------|------------------------|-----------------|
|  Player1 |                      5 |               2 |
|  Player2 |                      1 |               2 |
|  Player3 |                      2 |               0 |

These 3 steps should be done in 1 query:

Select PlayerID, Count(platform)as playedPlaystationGames, platform
From Data AS TablePlaystation
Group By PlayerID, platform
Having platform='Playstation';

Select PlayerID, Count(platform)as playedXBoxGames, platform
From Data AS TableXBox
Group By PlayerID, platform
Having platform='Xbox';

SELECT data.PlayerID, TableXBox.PlayedXBoxGames, TablePlaystation.playedPlaystationGames
FROM data
RIGHT JOIN (TablePlaystation
    RIGHT JOIN TableXBox
    ON TablePlaystation.PlayerID = TableXBox.PlayerID)
ON TablePlaystation.PlayerID = data.PlayerID;

CodePudding user response：

Use Group By

SELECT PlayerID,
          SUM(CASE WHEN platform = 'Playstation' THEN 1 ELSE 0 END) AS playedPlaystationGames,
          SUM(CASE WHEN platform = 'Xbox' THEN 1 ELSE 0 END) AS playedXBoxGames 
FROM data
GROUP BY PlayerID

Demo in sqlfiddle

Or another method that I do not recommend

SELECT T1.PlayerID,playedPlaystationGames,playedXBoxGames
FROM

  (Select PlayerID, Count(platform)as playedPlaystationGames, platform
  From Data AS TablePlaystation
  Group By PlayerID, platform
  Having platform='Playstation') t1
  
  LEFT JOIN
  
  (Select PlayerID, Count(platform)as playedXBoxGames, platform
  From Data AS TableXBox
  Group By PlayerID, platform
  Having platform='Xbox') t2 ON T1.PlayerID = T2.PlayerID