MySQL query help.

Question

SELECT username, (SUM(rating)/count(*)) as TheAverage, count(*) as TheCount 
FROM ratings 
WHERE month ='Aug' AND TheCount > 1 
GROUP BY username 
ORDER BY TheAverage DESC, TheCount DESC

I know that's really close (I think) but it's saying 'TheCount' doesn't exsist in the WHERE clause and the ORDER clause.

The table is:

id, username, rating, month

And I'm trying to work out the average rating for each user then order the results by average rating and number of ratings.

Please help.

Answer 1

SELECT username, (SUM(rating)/count()) as TheAverage, count() as TheCount 
FROM ratings 
WHERE month ='Aug'
GROUP BY username
HAVING TheCount > 1
ORDER BY TheAverage DESC, TheCount DESC

EDIT:

Seems I didn't look closely enough.

I think it'll work now.

Answer 2

Hi,

If you group and count, you need having:

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount
    FROM rating
    WHERE month='Aug'
    GROUP BY username
    HAVING TheCount > 1
    ORDER BY TheAverage DESC, TheCount DESC

Answer 3

You could use the AVG aggregate:

SELECT  username, month, AVG(rating) as TheAverage, COUNT(*) as TheCount
FROM    ratings
WHERE   month ='Aug'
GROUP BY
        username
HAVING  COUNT(*) > 1
ORDER BY
        TheAverage DESC, TheCount DESC

Grouping by month is innesessary in MySQL, since your month is filtered and MySQL supports selecting an ungrouped column in a SELECT list of a GROUP BY query (returning a random value within the group).