Subset table issues

Creating tables and queries

Subset table issues

Postby donnymioli » Wed Oct 28, 2015 10:04 pm

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.
OpenOffice 4.1.1 on MacOS 10.10.5
donnymioli
 
Posts: 4
Joined: Thu Sep 17, 2015 9:49 pm

Re: Subset table issues

Postby MTP » Thu Oct 29, 2015 9:29 pm

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   Expand viewCollapse view
SELECT "Players"."PlayerName", N - COALESCE(COUNT("Inactive"."PlayerName"),0)
   FROM "Players"
   INNER JOIN "Inactive" ON "Players"."PlayerName" = "Inactive"."PlayerName"
GROUP BY "Players"."PlayerName"
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest