MySQL query help.

Question

I have a mysql table called RATING with this structure:

ID, USERNAME, RATING, RATER, MONTH

I want to be able to show a league table of users ordered by their average rating for the current month.

For example:

1, Bob, 10, Rita, JUL

2, Bob, 8, Sue, JUL

3, Rita, 9, Bob, JUL

4, Sue, 4, Rita, JUL

5, Rita, 10, Sue, JUL

I want a SQL query which would produce these results:

Rita: 9.5

Bob: 9

Sue: 4

Any ideas please?

Many thanks

Oliver.

Answer 1

Assuming the month column is a datetime:

select username, avg(rating)
from rating
where YEAR(curdate()) = YEAR(rating.month)
and MONTH(curdate()) = MONTH(rating.month)
group by username

If it's the first 3 characters of the month, replace the WHERE statement with:

where LEFT(MONTHNAME(curdate()),3) = rating.month

Answer 2

select username, avg(rating), month from RATING group by username,month

Seeing that you have 'JUL' as your month, I'm assuming it's a varchar. If you just want the results for JUL:

select username, avg(rating) from RATING where month = 'JUL' group by username

Answer 3

SELECT `username`, AVG(`rating`) AS average FROM RATING GROUP BY `username`