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

SQL query to count replies in threads

#1
I'm trying to get 10 threads with more replies in one month.

What I'm doing wrong?

SELECT count(*)
FROM xf_post posts, xf_thread threads
WHERE posts.thread_id=threads.thread_id
AND posts.post_date
BETWEEN UNIX_TIMESTAMP( '2016-01-01 00:00:00' )
AND UNIX_TIMESTAMP( '2016-01-31 23:59:00' )
GROUP BY threads.thread_id
ORDER BY posts DESC
LIMIT 10
 

Dr.Yontem

Formerly Tilkißey
#5
Code:
SELECT thread_id
FROM xf_post
WHERE post_date
BETWEEN UNIX_TIMESTAMP("2016-01-01 00:00:01") AND UNIX_TIMESTAMP("2016-01-31 23:59:59")
GROUP BY thread_id
ORDER BY COUNT(*) DESC
LIMIT 10
use this one with editing dates

for getting reply counts

Code:
SELECT thread_id, COUNT(*) as thread_reply_count
FROM xf_post
WHERE post_date
BETWEEN UNIX_TIMESTAMP("2016-01-01 00:00:01") AND UNIX_TIMESTAMP("2016-01-31 23:59:59")
GROUP BY thread_id
ORDER BY thread_reply_count DESC
LIMIT 10