| Query: least 15 records in the last seven days
|
dsat
msg:4193266 | 8:44 pm on Aug 27, 2010 (gmt 0) |
How to make a query, if I want to find a userID who is at least 15 records in the last seven days of the table "example" field 'cID=2'.
table "example"
pID | cID | userID | rTime
1, 2, 34, 2010-11-04 16:36:29
2, 2, 27, 2010-11-04 18:45:02
...
|
Demaestro
msg:4193297 | 9:54 pm on Aug 27, 2010 (gmt 0) |
You want to make use of the count function and the having clause.
SELECT userID FROM table_name GROUP BY userID HAVING count(userID) > 14
Not sure if you need the condition or not but you can add in
where cID=2
If you need a condition you would add it like this.
SELECT userID FROM table_name WHERE cID = 2 GROUP BY userID HAVING count(userID) > 14
|
dsat
msg:4193314 | 10:28 pm on Aug 27, 2010 (gmt 0) |
I am interested in records that are entered into the last week (last seven days)
|
rocknbil
msg:4193519 | 5:34 pm on Aug 28, 2010 (gmt 0) |
Create table test (id int(11) primary key auto_increment, user int(11), post_date datetime);
insert into test(user,post_date) values(123,'2010-08-28 16:36:29');
insert into test(user,post_date) values(124,'2010-08-28 16:36:29');
insert into test(user,post_date) values(123,'2010-08-27 16:36:29');
insert into test(user,post_date) values(123,'2010-08-25 16:36:29');
insert into test(user,post_date) values(123,'2010-08-21 16:36:29');
/* remove this comment - note the following are older than 7 days */
insert into test(user,post_date) values(123,'2010-07-28 16:36:29');
insert into test(user,post_date) values(124,'2010-05-28 16:36:29');
insert into test(user,post_date) values(123,'2010-06-28 16:36:29');
select user from test where post_date >= date_sub(curdate(), interval 14 day) group by user;
-> 123
-> 124
select user, count(*) from test where post_date >= date_sub(curdate(), interval 14 day) group by user;
-> 123 4
-> 124 1
select user, count(*) from test where post_date >= date_sub(curdate(), interval 14 day) group by user having (count(*) >= 3);
-> 123 4
Adjust the bolded to 15 (better, use a configurable variable or form search input) and you got it. Just add your cid to the where.
Being datetime format, you can be more specific by using
date_sub(now(), interval 14 day)
|
|
|
