0

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

SOS
  • 6,430
  • 2
  • 11
  • 29
Lucica Savin
  • 9
  • 4
    1. You say you used the GROUP BY clause, but it's not included in your query 2. Please include some sample rows and the desired result, using those samples 3. You're missing a JOIN between the two tables, which will produce a [cartesian product](https://en.wikipedia.org/wiki/Join_(SQL)#Cross_join). 4. Don't use the antiquated ANSI 89 style use JOIN syntax: `FROM students s INNER JOIN register r ON r.SomeColumnName = s.SomeColumnName` – SOS Apr 06 '22 at 09:08
  • 1
    `WHERE YEAR(data_ora) = 2017` Though it's valid sql, expressions like that are not [sargable](https://en.wikipedia.org/wiki/Sargable). A better way to write that expression is `WHERE data_ora >= '2017-01-01' AND data_ora < '2018-01-01'` – SOS Apr 06 '22 at 09:26

1 Answers1

0

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
Xiidref
  • 1,456
  • 8
  • 20