sql help please

#1
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?
 
#3
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;
 

Marc

Well-known member
#5
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.
 
#6
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

Well-known member
#7
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.
 
Top