query help please!

[doubledown]

So here is my Query,

SELECT u.*, AVG(ur.rating) as rating, COUNT(ur.rating) as num_ratings

FROM users as u, users_ratings as ur

WHERE u.user_id = ur.for_user_id
group By ur.for_user_id

How could i edit this to only select users where COUNT(ur.rating) is greater than 10 and to order the results by AVG(ur.rating)

Thanks in advance

[Sxooter]

Look for the "having" keyword.

i.e.

select count(field) as count, avg(somotherfield) as AVG from table group by field having count(field)>10 order by AVG

[doubledown]

thanks, i'll look in to that!

[Sxooter]

The basic explanation is that the where part of the query 'fires' before the group by, and the having fires after, on the grouped data.

Also, you should avoid constructs like:

select u.*, count(field1) from table group by field1

since technically, there may be more than one u.* field for each row, and which one you're getting is not defined. I.e.:

table1:
id | num
1 | 1
1 | 2
1 | 1
1 | 3
2 | 1
2 | 2
2 | 4

select id, num from table1 group by id;

which num should be returned? 1, 2, 1, or 3 for id 1, or 3 1 2 or 4 for id 2? The correct way to do this (and a way that will work if you hit a database other than MySQL) is to use max/min:

select id, max(num) from table1 group by id;