SQL AVG() confusion basic SQL

Question

Part of my assignment says this 6. Display the student id and average score for each student.

This is what I came up with

SELECT student_id, AVG(score) FROM scores;

But it only brings up the average score of the first student. What do I need to change?

You should add GROUP_BY to perform aggregated function like AVG(). SELECT student_id, AVG(score) FROM scores GROUP BY student_id; — hgb123
– hgb123, Commented Dec 14, 2018 at 2:25
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html — P.Salmon
– P.Salmon, Commented Dec 14, 2018 at 7:47

jcyan · Accepted Answer · 2018-12-14 02:25:00Z

1

You need a groupby operator.

SELECT student_id, AVG(score) FROM (table_name) GROUPBY student_id

Maybe, the table like this,

|class_id|class_name|student_id|student_name|score|

You have to decribe your table in detail. Anyway, I think GROUPBY is all you need in this question.

answered Dec 14, 2018 at 2:25

jcyan

712 silver badges7 bronze badges

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

3 Comments

GROUPBY -> GROUP BY and don't put table_name in () (confusing).

you are right, my mistake. I don't know his table name, so I put it here.Maybe it's confusing.

the name is scores in the question. But generally abstract table_name can be inteprated correctly.

Revathi Ganesh · Accepted Answer · 2018-12-14 02:41:06Z

0

Please try this query with GROUP BY

SELECT student_id, AVG(score) FROM scores GROUP BY student_id

answered Dec 14, 2018 at 2:41

Revathi Ganesh

1191 silver badge6 bronze badges