CODEWITHSUNDEEP
sqlpostgresql
Dzamba
Dzamba

Reputation: 197

Merging two query results in a materialized view

Im trying to merge two SELECT results into one view. The first query returns the id's of all registered users. The second query goes through an entire table and counts how many victories a player has and returns the id of the player and number of wins.

What I'm trying to do now is to merge these two results, so that if the user has wins it states how many but if he doesn't then it says 0.

I tried doing it like this:

SELECT profile.user_id
 FROM profile
   FULL JOIN ( SELECT player_game_data.user_id,
          count(player_game_data.user_id) AS wins
         FROM player_game_data
        WHERE player_game_data.is_winner = 1
        GROUP BY player_game_data.user_id) t2 ON profile.user_id::text = t2.user_id::text;

But in the end it only returns id's of the players and there isn't a count column:

enter image description here

What am I doing wrong?

Upvotes: 1

Views: 913

Answers (2)

Dzamba
Dzamba

Reputation: 197

Thanks for the help Gordon. I've got it to work now.

The final query looks like this :

 SELECT p.user_id,
    ( SELECT count(*) AS count
           FROM player_game_data pg
          WHERE pg.user_id::text = p.user_id::text AND pg.is_winner = 1) AS wins,
    ( SELECT count(*) AS count
           FROM player_game_data pg
          WHERE pg.user_id::text = p.user_id::text AND pg.is_winner = 0) AS losses,
    ( SELECT count(*) AS count
           FROM player_game_data pg
          WHERE pg.user_id::text = p.user_id::text) AS games_played
   FROM profile p;

And when I run it I get the result that i wanted: enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Is this what you want?

select p.*,
       (select count(*)
        from player_game_data pg
        where pg.user_id = p.user_id and pg.is_winner = 1
       ) as num_wins
from profile p;

Or, if all users have played at least one game, you can use conditional aggregation:

select pg.user_id,
       count(*) filter (where pg.is_winner = 1)
from player_game_data pg
group by pg.user_id;

Or, if is_winner only takes on the values of 0 and 1:

select pg.user_id, sum(ps.is_winner)
from player_game_data pg
group by pg.user_id;

Upvotes: 2

Related Questions