Welcome to WebmasterWorld Guest from 54.242.83.7

Forum Moderators: open

Message Too Old, No Replies

Query: least 15 records in the last seven days

     
8:44 pm on Aug 27, 2010 (gmt 0)

New User

5+ Year Member

joined:Aug 27, 2010
posts: 4
votes: 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
...
9:54 pm on Aug 27, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 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
10:28 pm on Aug 27, 2010 (gmt 0)

New User

5+ Year Member

joined:Aug 27, 2010
posts:4
votes: 0


I am interested in records that are entered into the last week (last seven days)
5:34 pm on Aug 28, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members