tags:

views:

63

answers:

3
+1  Q: 

MySQL query help.

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.

+2  A: 
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.

Joel Potter
You just used `TheAverage`, which is an alias, in the order by clause.
Greg Hewgill
Thanks, but that doesn't seem to work. Has a problem with the count()
Oliver
+1  A: 

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
Rufinus
+1  A: 

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

Quassnoi