1seanr - 1 year ago 65
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
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.

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