title: MySQL multiple table query with average for each row
body: <p>This is my setup:</p>
  
  <ul>
  <li>Table "files": id (PK), filename, user_id, date, filesize</li>
  <li>Table "scores": id(PK), file_id, user_id, score</li>
  </ul>
  
  <p>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.</p>
  
  <p><code>SELECT files.*, (SUM(scores.score)/(COUNT(scores.score))) AS total FROM files INNER JOIN scores ON files.id=scores.file_id;</code></p>
  
  <p>Please point me in the right direction - thanks!</p>