Subset table issues

[donnymioli]

im working on a fantasy football database. I want to find a way to count the number of games played by each player to calculate mean points per game played.

I have a player table, and a table that counts each time a player has been inactive. I want to create a query that can say if your name is on the inactive table, you have played n-x games. if your name isnt on the table, you have played n games.

thanks in advance.

[MTP]

You'd have to replace N with a number, and replace my table name and column guesses with your actual table names and column names, but I think something like this query would work:

Code: Select all

SELECT "Players"."PlayerName", N - COALESCE(COUNT("Inactive"."PlayerName"),0)
   FROM "Players"
   INNER JOIN "Inactive" ON "Players"."PlayerName" = "Inactive"."PlayerName"
GROUP BY "Players"."PlayerName"