SQL: MS Access query results change when new inner join added

Question

Three tables:

Activities
Matches
Ratings

Each match is associated with one activity (AID as a foreign key). And each activity has multiple reviews.

I'm trying to count the number of matches that each activity is associated with:

SELECT MATCHES.AID, Count(MATCHES.AID) AS CountOfAID
FROM ACTIVITIES INNER JOIN MATCHES ON ACTIVITIES.AID = MATCHES.AID
GROUP BY MATCHES.AID;

Returns this just fine:

But as soon as I add the inner join to also include the average rating of each activity:

SELECT ACTIVITIES.[Activity Name], Count(MATCHES.AID) AS CountOfAID, 
Avg(RATINGS.Rating) AS AvgOfRating
FROM (ACTIVITIES INNER JOIN MATCHES ON ACTIVITIES.AID = MATCHES.AID) INNER 
JOIN RATINGS ON ACTIVITIES.AID = RATINGS.AID
GROUP BY ACTIVITIES.[Activity Name];

This happens:

How can I work around this?

This is a common error where people want the join on a shared key of some aggregations (each possibly involving joining) but they erroneously try to do all the joining then all the aggregating or aggregate over previous aggregations. Look at the join before aggregating. Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, May 28 '19 at 07:27
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, May 28 '19 at 07:30
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, May 28 '19 at 07:31

score 2 · Answer 1 · answered May 28 '19 at 04:48

Since both reviews and matches are joined to activity but there's no relationship between them, you're essentially creating a product of review-match per activity. One approach to get the result you want is to perform the grouping on subqueries for the matches and reviews (independently), and only then join them on the activities:

SELECT a.[Activity Name], CountOfAID, AvgOfRating
FROM   activities a
JOIN   (SELECT   aid, COUNT(*) AS CountOfAID
        FROM     matches
        GROUP BY aid) m ON a.aid = m.aid
JOIN   (SELECT   aid, AVG(rating) AS AvgOfRating
        FROM     ratings
        GROUP BY aid) r ON a.aid = r.aid

SQL: MS Access query results change when new inner join added

1 Answers1