SQL query to count replies in threads

foiovitor

Active member
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
 
You forget the
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
 
Top Bottom