mysql avg() not working as expected

Question

Hello,

I'm having some trouble with MySql right now. I have an query that works just fine, I'm using it for a while, but today I got stuck with this.

The query is:

select avg(valor), tipo_id, users_id, datetime from entries where users_id = '1' and tipo_id = 1 and date_format(datetime,"%Y-%m-%d") between "'2010-09-20" and "2010-10-20" and date_format(datetime,"%h:%i") between "11:59" and "18:59" and excluded= 'n'

The query return a avg value for valor field, that's ok. But, when I change the users_id value to 635 I can't get an avg() value. I have some data with this users_id, and they fit on datetime range.

I really don't know what may be wrong with my query, and with almost 700 users, this is the firts time that I see this happen.

Answer 1

Try adding a GROUP BY users_id

select avg(valor), tipo_id, users_id, datetime from entries
where
    users_id = '1'
    and
    tipo_id = 1
    and
    date_format(datetime,"%Y-%m-%d") between "'2010-09-20" and "2010-10-20"
    and
    date_format(datetime,"%h:%i") between "11:59" and "18:59"
    and
    excluded= 'n'
GROUP BY users_id

Answer 2

Run it without the avg(valor) part, see what are the results, maybe some of the valor data is the problem. Maybe you have a string or somthing else. And what exactly do you get as reply? Any error?