CODEWITHSUNDEEP
sqlmysql
Oliver
Oliver

Reputation: 839

MySQL query: work out the average rating for each user then order the results by average rating and number of ratings

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 exist 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.

Upvotes: 1

Views: 396

Answers (3)

Rufinus
Rufinus

Reputation: 30773

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

Upvotes: 1

Joel
Joel

Reputation: 19378

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.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425863

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).

Upvotes: 1

Related Questions