Simple SQL Select Query between 2 tables

I have 2 table

First table - userTable

usedID  userName
1          someName
2          someOthername

Second Table - ratingTable

userID ratingValu开发者_如何学Ce
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)

---

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

---

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
        u.UserID
order by
        count(r.ratingValue)

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

---

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
GROUP BY u.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