1 Reply Latest reply on Aug 19, 2014 8:36 AM by JohnGordon1972

    SQL query - using COUNT

    JohnGordon1972 Level 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

       

      Without any success. Any pointers would be much appreciated.