MySQL multiple table query with average for each row

Question

This is my setup:

• Table "files": id (PK), filename, user_id, date, filesize
• Table "scores": id(PK), file_id, user_id, score

Table "files" contains a list of files with details; table "scores" keeps track of 1-5 points scored per file. I need to get entries from the "files" table and in each row I need all the info for the file, as well as the average score. I can do another query for teh current file_id while I'm looping through the rows, but obviousely that's not very optimized. I tried something like below, but no success.

SELECT files.*, (SUM(scores.score)/(COUNT(scores.score))) AS total FROM files INNER JOIN scores ON files.id=scores.file_id;

Please point me in the right direction - thanks!

Answer 1

Aggregate functions are not usually useful without aggregation.

SELECT f.*, AVG(s.score) AS total
FROM files AS f
INNER JOIN scores AS s
  ON f.id=s.file_id
GROUP BY f.id

Answer 2

You may want to try the following:

SELECT f.id, f.filename, f.user_id, f.date, f.filesize,
       (
           SELECT AVG(s.score)
           FROM   scores s
           WHERE  s.file_id = f.id
       ) average_score
FROM   files f;

Note that you can use the AVG() aggregate function. There is no need to divide the SUM() by the COUNT().

Test case:

CREATE TABLE files (id int, filename varchar(10));
CREATE TABLE scores (id int, file_id int, score int);

INSERT INTO files VALUES (1, 'f1.txt');
INSERT INTO files VALUES (2, 'f2.txt');
INSERT INTO files VALUES (3, 'f3.txt');
INSERT INTO files VALUES (4, 'f4.txt');

INSERT INTO scores VALUES (1, 1, 10);
INSERT INTO scores VALUES (2, 1, 15);
INSERT INTO scores VALUES (3, 1, 20);
INSERT INTO scores VALUES (4, 2, 5);
INSERT INTO scores VALUES (5, 2, 10);
INSERT INTO scores VALUES (6, 3, 20);
INSERT INTO scores VALUES (7, 3, 15);
INSERT INTO scores VALUES (8, 3, 15);
INSERT INTO scores VALUES (9, 4, 12);

Result:

SELECT f.id, f.filename, 
       (
           SELECT AVG(s.score)
           FROM   scores s
           WHERE  s.file_id = f.id
       ) average_score
FROM   files f;

+------+----------+---------------+
| id   | filename | average_score |
+------+----------+---------------+
|    1 | f1.txt   |       15.0000 |
|    2 | f2.txt   |        7.5000 |
|    3 | f3.txt   |       16.6667 |
|    4 | f4.txt   |       12.0000 |
+------+----------+---------------+
4 rows in set (0.06 sec)

Note that @Ignacio's solution produces the same result, and is therefore another option.