SQL query using COUNT

Databases
1

I have a database structure like this:

Table - lodges
LodgeID (PK)
Lodge
etc

Table - scores
ScoreID (PK)
Score
CategoryID
LodgeID (FK)

I’m trying to return results in the form:

LodgeID, Lodge, Category, Number of Scores in that Category, Average Score in that Category

So for example, if I had:

lodges
LodgeID, Lodge
1, Lodge One
2, Lodge Two

scores
ScoreID, Score, CategoryID, LodgeID
1, 3, 101, 1
2, 5, 101, 1
3, 7, 101, 1
4, 10, 102, 2
5, 20, 102, 2
6, 30, 102, 2
7, 40, 102, 2

I’d like to return:

1, Lodge One, 3, 5
2, Lodge Two, 4, 25

I’ve been trying things like:

SELECT COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, Lodge FROM scores_temp INNER JOIN lodges_temp ON scores_temp.LodgeID = lodges_temp.LodgeID
SELECT lodges_temp.LodgeID, Lodge, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore FROM lodges_temp INNER JOIN scores_temp ON lodges_temp.LodgeID = scores_temp.LodgeID

Without any success. Any pointers would be much appreciated.

2

Ignore that - just needed to add a GROUP BY clause.