Problem with Innerjoin and count.

  • Thread starter Thread starter Jimmy Smits
  • Start date Start date
J

Jimmy Smits

Hello,

I have 3 tables
tblParticipants:
Group
UserID(Primary)
Username
PW
Email

TblPicks:
Pickvalue(primary autonumber)
PlayerID
Pick1,Pick2...Pick63.

TblResults:
Pickvalue(primary Autonumber)
Pick1, Pick2...Pick63.

I am trying to find a way to compare the two databases so that I can count
the number of correct picks for each player in the picks table, compared to
the results table. I have been pulling my hair out for a couple of days over
this. Here is the sql I have:

SELECT P.playerID, Count(P.PickID) AS Hits
FROM Picks AS P INNER JOIN Results AS R ON (P.PickValue=R.PickValue) AND
(P.PickID=R.PickID);

I get the following error.
"you tried to execute a query that does not include P.playerid as part of an
aggregate function"

Thanks for reading.
 
Back
Top