1seanr - 6 months ago 28

MySQL Question

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.

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
```