Select element with maximum average

Question

In my database i have the following schema:

table: files
columns: fileId, authorId, fileSize

table: authors
columns: authorId, surname, firstName

Now id like to select the authorId of the author which has the highest average of fileSize. I am a bit clueless right now on how i best join these tables. I have already been able to select the average fileSize for an author, but now must extend that to work with each author.

SELECT AVG(`fileSize`) FROM (
    SELECT `fileSize`, `files`.`authorId` FROM `files` 
        INNER JOIN `authors` aut ON `files`.authorId = aut.authorId 
    WHERE aut.authorId = 6) 
as T;

What DB server are you using? – Richard Deeming Nov 21 '13 at 17:05 — Richard Deeming, Nov 21 '13 at 17:05

score 2 · Accepted Answer · answered Nov 21 '13 at 17:53

If only the authorId is needed, you can find it through the files table only:

SELECT authorId
FROM files
GROUP BY authorId
ORDER BY AVG(fileSize) DESC
LIMIT 1 ;

If you want other data for this author as well, you can male the previous a derived table and join it to authors:

SELECT a.*                         -- the columns you need
FROM authors AS a
  JOIN
  ( SELECT authorId
    FROM files
    GROUP BY authorId
    ORDER BY AVG(fileSize) DESC
    LIMIT 1 
  ) AS m
  ON m.authorId = a.authorId ;

score 1 · Answer 2 · edited May 23 '17 at 12:19

The following should give you a list of your authors in order of average file size.

SELECT AVG(files.fileSize), aut.authorId, aut.firstname, aut.surname
FROM  files 
INNER JOIN authors aut ON files.authorId = aut.authorId 
GROUP BY aut.authorId
ORDER BY AVG(files.fileSize) DESC;

SQL Fiddle example

You can leave the AVG(files.fileSize) out of the select and it will still work.

If you just want just the one maximum (or all the ties for max, if they happen to exist), you'll need to dig a tiny bit deeper.
The following Stack Overflow question should help.
SQL Select only rows with Max Value on a Column

Select element with maximum average

2 Answers2