Sum By Group in Access 2016 Query

I have a query that returns the following data:

I am trying to adapt it to sum the total number of runs at each venue. I have come up with the query below but there are two issues:

1. it is not summing by venue it is summing all runs;

2. it takes a very long time to run.

Can anyone see what I’m doing wrong?

Thanks

SELECT ScorecardBatting.matchId, ScorecardBatting.battingTeam, ScorecardBatting.inningsNo, ScorecardBatting.batsmanId, ScorecardBatting.howDismissed, ScorecardBatting.runs, ScorecardBatting.ballsFaced, Matches.venue, (SELECT SUM(runs) FROM ScorecardBatting WHERE Matches.venue=Matches.venue) AS TOTAL
FROM Matches INNER JOIN ScorecardBatting ON Matches.matchId = ScorecardBatting.matchId
GROUP BY ScorecardBatting.matchId, ScorecardBatting.battingTeam, ScorecardBatting.inningsNo, ScorecardBatting.batsmanId, ScorecardBatting.howDismissed, ScorecardBatting.runs, ScorecardBatting.ballsFaced, Matches.venue;

Answer

If you want the total number of runs for each venue, then a simple aggregation query does what you want:

SELECT Matches.venue, SUM(runs) AS TOTAL
FROM Matches INNER JOIN
     ScorecardBatting
     ON Matches.matchId = ScorecardBatting.matchId
GROUP BY Matches.venue;

If you want this in your original query, you could join it in:

select . . ., t.total
from Matches inner join
     ScorecardBatting
     on Matches.matchId = ScorecardBatting.matchId join
     (select Matches.venue, sum(runs) AS TOTAL
      from Matches INNER JOIN
           ScorecardBatting
           on Matches.matchId = ScorecardBatting.matchId
      group by Matches.venue
     ) t
     on t.venue = matches.venue;

I don’t think you need a group by fro your query.