How to group by a calculated average
<p>I currently have the statement</p>

<pre><code>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
</code></pre>

<p>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 </p>

<p>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.</p>

<p>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</p>

<p>I have tried</p>

<pre><code>GROUP BY R.ItemID, Avg(DifRating)
</code></pre>

<p>and</p>

<pre><code>GROUP BY R.ItemID, AvgDifficuilty
</code></pre>

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

<p><strong>TLDR: how do i group by an average number</strong></p>

<p>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.</p>