SQL Query - using COUNT

Question

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.

You should look into GROUP BY.

Azhdeen
– Azhdeen

2014-08-19 15:17:33 +00:00
Commented Aug 19, 2014 at 15:17 — Azhdeen
– Azhdeen, Commented Aug 19, 2014 at 15:17
did you add Group By Lodge at the end?

Matt
– Matt

2014-08-19 15:17:39 +00:00
Commented Aug 19, 2014 at 15:17 — Matt
– Matt, Commented Aug 19, 2014 at 15:17

Matt · Accepted Answer · 2014-08-19 15:18:33Z

1

Try this

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

answered Aug 19, 2014 at 15:18

Matt

3,6802 gold badges29 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2014-08-19 15:19:19Z

0

You are missing a group by clause:

SELECT     COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, Lodge 
FROM       scores
INNER JOIN lodges ON scores.LodgeID = lodges.LodgeID
GROUP BY   lodge

answered Aug 19, 2014 at 15:19

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

user17508 Over a year ago

Thank you! I always forget about Group By!

Collectives™ on Stack Overflow

SQL Query - using COUNT

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related