How to get maximum and average of a field from mysql table with some conditions

Question

I have a table with records like following.

id     score      student_id      course_id
----   ------     ----------      ----------
1      12         1               1
2      10         2               1
3      20         3               1
4      35         1               2
5      50         5               1
6      70         1               3
7      25         3               2
8      30         2               2
9      25         3               3

Currently i am running this query first

SELECT id,
       score,
       student_id,
       course_id
FROM scores
WHERE student_id=1

The output of the query is

id  student_id   course_id  score 
--  ----------   ---------  -----
1   1            1          12    
4   1            2          35    
6   1            3          70

THEN running LOOP ON RESULT AND finding avg_score, max_score USING this query

SELECT AVG(score) AS avg_score,
         MAX(score) AS max_score
  FROM scores WHERE course_id = RESULT['course_id']

and then adding them in the result. So finally i get the result with avg_score and max_score like this

id  student_id   course_id  score  avg_score  max_score
--  ----------   ---------  -----  ---------  ---------
1   1            1          12     23         50
4   1            2          35     30         35
6   1            3          70     47.50      70

I want to know if it is possible to get the result using a single query instead of as i am doing right now.

I have tried this query but i get only 1 row instead of 3. Also the avg_score and max_score are wrong.

SELECT Score.id, Score.student_id, Score.course_id, Score.score, ROUND(AVG(mScore.score),2) as avg_score, MAX(mScore.score) as max_score 
FROM `scores` AS `Score` 
LEFT JOIN `scores` as `mScore` ON mScore.course_id = Score.course_id 
WHERE Score.student_id = 1

Output of the above query is

id  student_id   course_id  score  avg_score  max_score
--  ----------   ---------  -----  ---------  ---------
1   1            1          12     30.78      70

sqlfiddle link is http://sqlfiddle.com/#!2/8a3f1f/8

If it is not achievable using single query but there is better alternative than what i am doing now, please let me know.

PS: Here is the jsfiddle link of actual problem. http://sqlfiddle.com/#!2/c50aa9/1/0 It has two tables. I simplified and combined two table into one in the question. The single query i was trying gives only one row where it should give 4 rows like this query without avg_score and max_score http://sqlfiddle.com/#!2/c50aa9/3/0

Answer 1

Following query solved the puzzle.

SELECT Score.id, Score.student_id, Score.course_id, Score.score, ROUND(AVG(mScore.score),2) as avg_score, MAX(mScore.score) as max_score 
FROM `scores` AS `Score` 
LEFT JOIN `scores` as `mScore` ON mScore.course_id = Score.course_id 
WHERE Score.student_id = 1 GROUP BY Score.id

http://sqlfiddle.com/#!2/8a3f1f/10

If still there is any better solution, please let me know.

Answer 2

SELECT id, score, student_id, course_id,  
       AVG(score) as avg_score, 
       MAX(score) as max_score
FROM scores 
WHERE student_id = 1
group by id, score, student_id, course_id 

And if you remove the where condition then you can get this for all students at once.