CODEWITHSUNDEEP
mysqlsql
1seanr
1seanr

Reputation: 706

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.

Upvotes: 0

Views: 42

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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

Upvotes: 1

Related Questions