Group by with count not working

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

I have three tables:
Teams(TID,...)
Players(TID<PID<....)(1:M on teams)
Games(TID<GID<....)(1:M onteams)

I want to write a query that gives me the number of players and number of
games for each team
If I use the group by clause on TID and the count on just one of the tables
, I get the correct counts Players(4), Games(3)
However If I write a single query using all three tables, I get different
results.

The sql is:

SELECT Teams.TID, Count(Games.GID) AS CountOfGID, Count(Players.PID) AS
CountOfPID
FROM (Teams LEFT JOIN Games ON Teams.TID = Games.TID) LEFT JOIN Players ON
Teams.TID = Players.TID
GROUP BY Teams.TID;

I cant figure out what is wrong.

Thanks for any help

Jack
 
Perhaps this may work for you:

SELECT Teams.TID, Count(Games.GID) AS CountOfGID,
(SELECT Count(P.*) FROM Players AS P
WHERE P.TID = Teams.TID) AS CountOfPlayers
FROM Teams INNER JOIN Games
ON Teams.TID = Games.TID
GROUP BY Teams.TID;
 
Ken,
I typed exactly what you suggested and it worked. But I must admit I don't
understand it. I have two questions:
1)Why didn't the original sql work
2) I don't understand the syntax "SELECT count(P.*) from Players AS P"
Specifically count(p.*)

Thanks.
Jack
 
The reason your SQL doesn't always return the right counts is because the
joins that you use return a record for each record in Games and for each
record in Players that has the TID value of the record in Teams. Thus, if
you have 1 record in Games and 2 in Players, you'll get 2 records for each
TID, not one, and thus the counts will be twice as many. Run your query
again and then look at the number of records for each TID in Games and in
Players...your counts for each TID should be the multiple of the two numbers
of records.

The SQL that I provided uses a subquery as one of the two count fields so
that you get a single join from Teams to Players, without the duplicating
effect that can result from having Games in that join as well.

The P.* syntax is because the subquery is using P as an alias for the
Players table name, and P.* means to count all records returned by the
subquery from the aliased table P regardless of whether all fields in the
records have values. Just like you would use Count(*) as a shorthand for
Count(Games.*) if you wanted to get count of all records from Games instead
of just those records that have a value for GID.
 
Ken,
Thanks for the great detiled explanation. I surely could not have figured
out the affect of my original query.
Thanks for your help
Jack
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Jack said:
Ken,
Thanks for the great detiled explanation. I surely could not have figured
out the affect of my original query.
Thanks for your help
Jack
Click to expand...
 
Back
Top