Reputation: 839
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
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
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
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