Finance Law

Create two aggregated virtual tables from one table and join

By admin

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;