MySQL query to return rows ordered by averages from another table-CodePudding

I am building a database for a contest in which people upload photos and the jury grades them. I have two tables: photographs (id, name, user_id and section_id) and grades (id, grade, photo_id, juror_id). What I want to achieve is a query to return all photos ordered by the average of all grades given to each photo.

For example, if we have 2 photographs with ids 1 and 2 with photo 1 having two grades (5, 6) and photo 2 also having two grades (8, 10) the first returned row will be the photo with id 2 (the average of the grades is 9 and it is greater than 5.5, the average of photo 1).

How could I achieve this?

Here is a pseudo-example of a query

SELECT * FROM photographs ORDER BY AVERAGE(SELECT grade FROM grades)

CodePudding user response：

This is a job for AVG() and GROUP BY.

To get the average grade by photo from your grades table this subquery does it.

                 SELECT AVG(grade) avg_grade,
                        photo_id
                   FROM grades
                  GROUP BY photo_id

That subquery is guaranteed to return exactly one row per photo_id value. So you can LEFT JOIN it to your photographs table like so.

SELECT avg_grade.avg_grade,
       photographs.*
  FROM photographs
  LEFT JOIN (
                 SELECT AVG(grade) avg_grade,
                        photo_id
                   FROM grades
                  GROUP BY photo_id
       ) avg_grade ON photographs.id = avg_grade.photo_id
 ORDER BY avg_grade.avg_grade DESC

CodePudding user response：

First of all you need to join your table properly, then to agreagat result, and after that to order output:

SELECT
    p.id,
    AVG(g.grade) AS averageGrade
    FROM photographs AS p
    JOIN grade AS g ON g.photo_id = p.id
    GROUP BY p.Id
    ORDER BY AVG(g.grade) DESC