sql help please

[TerminalAddict]

ok .. I would like to be able to execute a query that give me every user that has 20 post.

here's some background that I've got

select distinct(username) from xf_post;
Gives me all users that have posted.

select count(username) from xf_post where username='TerminalAddict';
gives me a the number of posts I have made.

post_date is an int presumably with a timestamp.

I'd like a query that does:
select username, post count, and userid for all users that have a posted more than 20 posts between date 1 and date 2

usage: tell me who has made 20 posts in the last month.

thoughts?

 

[ragtek]

all users with 20 posts:

select username from xf_user where message_count = 20

 

[TerminalAddict]

all users with 20 posts:

nice .. duh I was looking the wrong table for post counts
Still, how about dates? between date1 and date2

this doesn't work btw:
select u.username,u.user_id from xf_user as u join xf_post as p where post_date > $date1 and post_date < $date2;

 

[ragtek]

p.post_date

 

[Marc]

LOL ... and I just looked in the same table.

Just incase your interested, could get it the way you were after like this

==================================
SELECT username,count(username)
FROM `xf_post`
WHERE FROM_UNIXTIME(`post_date`) BETWEEN '20110101' AND '20110131'
GROUP BY username
HAVING count(username) >19
===================================

There is probably a better way using the unix date, however not used to using these as I work with sql server rather than mysql.

 

[TerminalAddict]

LOL ... and I just looked in the same table.

Just incase your interested, could get it the way you were after like this

==================================
SELECT username,count(username)
FROM `xf_post`
WHERE FROM_UNIXTIME(`post_date`) BETWEEN '20110101' AND '20110131'
GROUP BY username
HAVING count(username) >19
===================================

There is probably a better way using the unix date, however not used to using these as I work with sql server rather than mysql.

perfect !! thanks

SELECT username,count(username) as post_count FROM `xf_post` WHERE FROM_UNIXTIME(`post_date`) BETWEEN '20110101' AND '20110201' GROUP BY username HAVING count(username) >19 order by post_count desc;

it's the HAVING bit that I have not used a trillion years that did it

 

[Marc]

No problem ... Would be very interested if anyone can tell me the more efficient ways of doing this rather than using the functions as Im sure there must be one.