CODEWITHSUNDEEP
mysql
Ken
Ken

Reputation: 636

Need help building a join query to return the top commenters

I'm trying to return the top five commenters in the month of feburary using a single sql query, but I'm having trouble understanding how I am to incorperate the join query.

Here's what my table looks like:

+--------+--------+----------+------------+---------+
| postid | userid | username | dateline   | comment |
+--------+--------+----------+------------+---------+
| 142535 |   5257 | oshatz   | 1094462853 | ....... | 
+--------+--------+----------+------------+---------+

And this is the query I've started. I think I'm close, but I can't figure it out.

SELECT username, j.total
  FROM post as post
  JOIN (SELECT count(postid) as total
          FROM post
         WHERE userid = post.userid 
           AND dateline > 1328054400 
           AND dateline < 1330473600) as j
 WHERE dateline > 1328054400 
   AND dateline < 1330473600
 GROUP
    BY userid
LIMIT 10

I'd like the results to look like this

+----------+----------+
| username | total    |
+----------+----------+
| Ken      |      149 |
+----------+----------+
| Bob      |      102 |
+----------+----------+
| Cindy    |      100 |
+----------+----------+
| Linsy    |       96 |
+----------+----------+
| Greg     |       85 |
+----------+----------+

Can someone help build that query for me? Thanks!

Upvotes: 1

Views: 30

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

SELECT username, count(postid) as total
FROM post
WHERE dateline > 1328054400 AND dateline < 1330473600
GROUP BY userid, username
ORDER BY total DESC
LIMIT 10

Upvotes: 1

Related Questions