sql - MySQL 查询 : work out the average rating for each user then order the results by average rating and number of ratings

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

我知道这非常接近(我认为)，但它表示“TheCount”不存在于 WHERE 子句和 ORDER 子句中。

表格是:

id, username, rating, month

我正在尝试计算每个用户的平均评分，然后按平均评分和评分数量对结果进行排序。

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

编辑:

看来我看的不够仔细

我认为它现在可以工作了。