mySql sum a column and return only entries with and entry in last 10 minutes

Question

heres a table, the time when the query runs i.e now is 2010-07-30 22:41:14

number | person | timestamp
45       mike     2008-02-15 15:31:14
56       mike     2008-02-15 15:30:56
67       mike     2008-02-17 13:31:14
34       mike     2010-07-30 22:31:14
56       bob      2009-07-30 22:37:14
67       bob      2009-07-30 22:37:14
22       tom      2010-07-30 22:37:14
78       fred     2010-07-30 22:37:14

Id like a query that can add up the number for each person. Then only display the name totals which have a recent entry say last 60 minutes. The difficult seems to be, that although its possible to use AND timestamp > now( ) - INTERVAL 600, this has the affect of stopping the full sum of the number.

the results I would from above are

Mike 202
tom  22
fred 78

bob is not included his latest entry is not recent enough its a year old! mike although he has several old entries is valid because he has one entry recently - but key, it still adds up his full 'number' and not just those with the time period.

go on get your head round that one in a single query ! and thanks

andy.

Answer 1

You want a HAVING clause:

select name, sum(number), max(timestamp_column)
from table
group by name
HAVING max( timestamp_column) > now( ) - INTERVAL 600;

Answer 2

andrew - in the spirit of education, i'm not going to show the query (actually, i'm being lazy but don't tell anyone) :).

basically tho', you'd have to do a subselect within your main criteria select. in psuedo code it would be:

select person, total as (select sum(number) from table1 t2 where t2.person=t1.person) 
from table1 t1 where timestamp > now( ) - INTERVAL 600

that will blow up, but you get the gist... jim