MySQL - a list of the students with their average grade in a specific year in a descending way

Question

I am new to Mysql and I am trying to find the answer to a request but I am having difficulty finding the correct sequence:

I created the tables below, then I inserted values into the two tables.

CREATE TABLE students (
   ids int AUTO_INCREMENT PRIMARY KEY,
   name varchar(100) DEFAULT NULL,
   surname varchar(100) DEFAULT NULL
);

CREATE TABLE register (
   idr int AUTO_INCREMENT PRIMARY KEY,
   data_ora datetime DEFAULT NULL,
   idstu int DEFAULT NULL,
   grade int DEFAULT NULL,
   INDEX idstu (idstu),
);

Now, I have the following request: the list of students and their averages grade ordered by average grade decreasing in 2017;

I tried this:

SELECT name, surname, avg(grade) as average 
FROM   students, register
WHERE  YEAR(data_ora) = 2017
ORDER BY average DESC;

But I only get 1 name even though I have inserted more than 100 values.

Any help will be much appreciated!

Thanks!

Used the average function and the group by clause

Answer 1

You're missing two things here, first you should specify where is the link between the student table and the register table. For this, we use the JOIN statement in the request below.

The second thing is that avg will return one value per group of line, by default a request return many lines in a single group and avg will return only one value for all of them. So you need to separate each student in a different group to get the average for each student. For this, we use the GROUP BY statement in the request below.

SELECT name, surname, avg(grade) as average
FROM student 
JOIN register ON student.ids = register.idstu
WHERE  YEAR(data_ora) = 2017
GROUP BY student.ids