1seanr 1seanr - 1 month ago 6
MySQL Question

How to group by a calculated average

I currently have the statement

SELECT ItemName, CONCAT(FirstName, ' ' , LastName) as AuthorName, Avg(DifRating) as 'AvgDifficuilty', Avg(EnjRating) as 'AvgEnjoyment', I.ItemID, A.AuthorID
FROM Rating R
INNER JOIN Item I ON R.ItemID = I.ItemID
INNER JOIN Author A ON I.AuthorID = A.AuthorID
INNER JOIN UserAccount UA ON A.UserID = UA.UserID
GROUP BY R.ItemID


but the problem is that instead of having the AvgDifficuilty and AvgEnjoyment being seperate it is oddly combining the maths of them but displaying them seperate

so if for example i have the vales 3 and 4 for the DifRatings and also a 3 and 4 for the EnjRatings i get a 3.5 for the Dif which is correct but then a 4 for the Enj.

And I have to group by ItemID so I think the solution to this problem is to also group buy the 2 averages so that they are calculated correctly but I cant seem to get it to be able to group by an average number

I have tried

GROUP BY R.ItemID, Avg(DifRating)


and

GROUP BY R.ItemID, AvgDifficuilty


but both just give errors so I don't know how to do it.

TLDR: how do i group by an average number

Any help would be greatly appreciated and sorry if I have done a bad job explaining ill try answer any questions/fill in blanks cause i dont know if what i want is even possible.

Answer

You should compute the averages from the Rating table first, before you them to the other tables:

SELECT i.ItemName,
       i.ItemID,
       a.AuthorID,
       CONCAT(a.FirstName, ' ', a.LastName) AS AuthorName,
       t.AvgDifficulty,
       t.AvgEnjoyment
FROM
(
    SELECT ItemID,
           AVG(DifRating) AS AvgDifficulty,
           AVG(EnjRating) AS AvgEnjoyment
    FROM Rating
    GROUP BY ItemID
) t
INNER JOIN Item i
    ON t.ItemID = i.ItemID
INNER JOIN Author a
    ON i.AuthorID = a.AuthorID
INNER JOIN UserAccount ua
    ON a.UserID = ua.UserID
Comments