Home > database >  Trying to add a DISTINCT COUNT column in table to INNER JOIN query SQLite
Trying to add a DISTINCT COUNT column in table to INNER JOIN query SQLite

Time:08-17

So I am querying a basketball dataset and I'm using an INNER JOIN statement to get the season stat lines for each Rockets player. However, I am also trying to get a count of how many games they played in the season putting that in a column to get a count of games played for each player.

I'm still learning so I'm trying to experiment and put queries together until I'm stumped. Having trouble with nested queries, thank you in advance.

Pictures: ER Diagram, Error Message

-- Houston Rockets team_id = 1610612745
-- Jalen Green player_id = 1630224
SELECT player.player_name, player_season.*
FROM player_season
(
SELECT 
DISTINCT COUNT (player_id)
FROM player_game_log
AS games_played
)
INNER JOIN
    player
    ON player_season.player_id = player.player_id
WHERE player_season.team_id = 1610612745

CodePudding user response:

Try adding the subquery directly in the SELECT statement like this:

SELECT player.player_name,
       (SELECT DISTINCT COUNT(player_id)
        FROM   player_game_log
        WHERE  player_game_log.player_id = player.player_id) AS games_played,
       player_season.*
FROM   player_season
       INNER JOIN player
               ON player_season.player_id = player.player_id
WHERE  player_season.team_id = 1610612745 
  • Related