• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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.