Take the 2-minute tour ×

I have 2 table

First table - userTable

usedID  userName
1          someName
2          someOthername

Second Table - ratingTable

userID ratingValue
1          5
1          3
1          5
1          3
2          5
2          5
2          3
2          5

I need to write a SQL query that will get all userID in ascending order for number of times rated (5 star)

share|improve this question
Well, that's very simple. What have you done so far? –  Vincent Savard Nov 30 '10 at 22:50
add comment

3 Answers

up vote 1 down vote accepted
SELECT u.userID, u.userName, COUNT(*) AS ratingCount
    FROM userTable u
        INNER JOIN ratingTable r
            ON u.userID = r.userID
                AND r.ratingValue = 5
    GROUP BY u.userID, u.userName
    ORDER BY ratingCount
share|improve this answer
Thanks it worked –  vivianaranha Nov 30 '10 at 22:56
add comment

Here's one example:

select  u.UserId
,       count(r.ratingValue)
from    userTable u
left join
        ratingTable r
on      u.userID = r.userID
        and r.ratingValue = 5
group by
order by

If the result does not require users without any five star ratings, you can even omit the userTable altogether.

share|improve this answer
add comment

I assume you mentioned 5 stars as the rating system you are using and not that you only wish to retrieve users with ratings of 5 stars.

SELECT u.userName, avg( r.ratingValue ) as averageRating
FROM userTable u
LEFT JOIN ratingTable r ON u.userID = r.userID
ORDER BY avg( r.ratingValue ) desc

This will get the average rating of each user and display their names.

userName averageRating
test1   4.5000
test2   1.7500
share|improve this answer
add comment

Your Answer


