homepage Welcome to WebmasterWorld Guest from 54.234.59.94
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved