Postgresql – Using Select Alias in where clause

aggregatepostgresql

I have a table games

+------------------+----+
|      title       | id |
+------------------+----+
| Need for Speed   |  1 |
| Far Cry          |  2 |
| Assassin's Creed |  3 |
+------------------+----+

and I have a table games_played

+-----------+---------+
| player_id | game_id |
+-----------+---------+
|         1 |       1 |
|         1 |       2 |
|         2 |       3 |
|         2 |       1 |
|         3 |       2 |
|         3 |       1 |
+-----------+---------+

What I need here is select all the games that player_id 1 haven't played and also the number of times the game was totally played. 

+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

Here is the query I wrote

select * from games, count("games_played.*"),
exists(select games_played.player_id from games_played where 
games_played.player_id = 1 and game_id = games.id) as played,
inner join games_played
on games_played.game_id = games.id
where played = false;

This is throwing and error column played does not exist.

Can someone help me here ?

Best Answer

There are several issues with the query. FROM and column expression are misplaced, "games_played.*" is not doing what it looks like, inner join instead of outer join, etc.

Try this one (I assume that (id) is the primary key of games):

select
    games.*, 
    count(games_played.*)
        as count,
    count(*) filter (where games_played.player_id = 1) > 0
        as played
from games
    left join games_played
    on games_played.game_id = games.id
group by 
    games.id
order by
    games.id ;

Test at dbfiddle.uk

Related Question