Trying to use ORDER BY with average values

I need to sort values from an SQL table according to the averages of certain values.

My 'Exams' table looks like this:

+------------+------------+------+
| Exam       | Student_ID |Points|
+------------+------------+-------
| Math       |     3      | 20   |      
|Accounts I  |    23      | NULL | 
|Statistics  |    12      | 15   |
|Accounts II |     3      | 21   |
+------------+------------+------+

Here's the query I've attempted:

SELECT Student_ID FROM Exams G开发者_如何学CROUP BY Student_ID ORDER BY avg(POINTS) desc

My aim is to get the student ID of the student with the highest average rating, in this case, 3

Is my approach correct, or is there an error lurking in there? How will I be able to handle NULL values in the Points column?

You have 2 options :

Don't count NULL's :

SELECT Student_ID, AVG(Points) AS apoints 
FROM Exam
WHERE Points IS NOT NULL
GROUP BY Student_ID 
ORDER BY apoints DESC

Assume NULL is a number (in this case 0) :

SELECT Student_ID, AVG(COALESCE(Points,0)) AS apoints 
FROM Exam
GROUP BY Student_ID 
ORDER BY apoints DESC

Aggregate functions such as average require another column containing result of the aggregate function when combined with GROUP BY.

So try:

SELECT Student_ID, avg(POINTS) AS avg_points 
FROM Exams 
GROUP BY Student_ID 
ORDER BY avg_points desc