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.