help counting distinct values

joo-joo-man

New member
Local time
Today, 08:47
Joined
Oct 17, 2010
Messages
2
Hi there,

I'm a total beginner at SQL and Access and I'm having trouble writing a query. Hopefully somebody will steer me in the right direction and I will learn something in the process.

I am working with a Chess database that tracks clubs, players, tournaments etc. I need to write a query to extract the player id, name, number of tournaments played in and the number of placings (1st, 2nd, 3rd) won for each player.

There are usually different events at each tournament, so a player can technically gain more than one placing at a single tournament (e.g. 1st in one event, 2nd in another for example).

Therefore, I want to extract only the unique number of tournaments a player has entered, and the total number of placings they have achieved (e.g. Bob Smith entered 3 tournaments and achieved 5 total placings).

The way I have my query set up now (see below), it would extract 5 tournaments entered and 5 total placings achieved for Bob Smith. My question is, how can I make my query only select the distinct number of tournaments and placings?


SELECT Player.Player_ID AS ID, (Player_lname + ", " + Player_fname) AS NAME, COUNT(Tournament_ID) AS numOfTournaments, COUNT(Placing_ID) AS numOfPlacings
FROM Player, Representative, Contestant, Placing
WHERE Player.Player_ID = Representative.Player_ID AND Representative.Rep_ID = Contestant.Rep_ID AND Contestant.Cont_ID = Placing.Cont_ID
GROUP BY Player.Player_ID, Player_fname, Player_lname
ORDER BY COUNT(Placing_ID) DESC;
 
You seem to be suggesting that a single player can have the same tournamentID appear multiple times in the table
TournamentID.....Placing
1..........................4
1..........................3
1..........................1

In that case you will probably need to group by TournamentID as to get an aggregate count of how many tournaments - well, probably both a DISTINCT and a GROUP BY on tournamentID. Maybe I'll give this a whirl....
 
Would be helpful if I knew which table to pull the number of tournaments from (otherwise I'll end up joining more tables than necessary).
 
I think the basic idea to get the number of tournaments is the following (possibly with more joins than needed):

SELECT PlayerID, COUNT(Tournament_ID) AS numOfTournaments
FROM
(
SELECT DISTINCT Player.PlayerID, tournament_ID
FROM Player, Representative, Contestant, Placing
WHERE Player.Player_ID = Representative.Player_ID AND Representative.Rep_ID = Contestant.Rep_ID AND Contestant.Cont_ID = Placing.Cont_ID
) as Tourns
GROUP BY PlayerID

But you also want the number of placings in the same query. Convince me you are still out there monitoring this thread and maybe I'll finish the job.
 
Thanks jal, that works to pull the distinct number of tournaments out. I've played around a bit more but still have the same problem - whenever I add anything to count the placings it screws up the tournament count.

My table structures are as follows:

Player(ID, Lname, Fname, DOB, Gender, Club_ID)
Competition(Comp_ID, Event_ID, Tournament_ID)
Contestant(Cont_ID, Rep_ID, Comp_ID)
Club(Club_ID, Club_name)
Event(Event_ID, Event_Title)
Tournament(Tourn_ID, Tourn_year, Tourn_website, Tourn_cancel, Club_ID)
Placing(Placing_ID, Placing_position, Cont_ID)
Representative(Rep_ID, Player_ID, Club_ID, Tournament_ID)
 
The following is untested (and probably has more joins than necessary)

SELECT NumPlacings.*, NumOfTournaments FROM
(
SELECT Player.Player_ID AS ID, (Player_lname + ", " + Player_fname) AS NAME, COUNT(Placing_ID) AS numOfPlacings
FROM Player, Representative, Contestant, Placing
WHERE Player.Player_ID = Representative.Player_ID AND Representative.Rep_ID = Contestant.Rep_ID AND Contestant.Cont_ID = Placing.Cont_ID
GROUP BY Player.Player_ID, Player_fname, Player_lname
) as NumPlacings
INNER JOIN
(
SELECT PlayerID, COUNT(Tournament_ID) AS numOfTournaments
FROM
(
SELECT DISTINCT Player.Player_ID, tournament_ID
FROM Player, Representative, Contestant, Placing
WHERE Player.Player_ID = Representative.Player_ID AND Representative.Rep_ID = Contestant.Rep_ID AND Contestant.Cont_ID = Placing.Cont_ID
) as Tourns
GROUP BY Player_ID
) AS NumTournaments
ON NumTournaments.Player_ID = NumPlacings.Player_ID
 

Users who are viewing this thread

Back
Top Bottom