CODEWITHSUNDEEP
mysqlcount
ChrisMe
ChrisMe

Reputation: 55

show results with biggest count mysql

I need to show user with the most comments. I have two tables:

Table: Users

ID   |  USERNAME   |    EMAIL
------------------------------
1    |   USER01    |   EMAIL01
2    |   USER02    |   EMAIL02
3    |   USER03    |   EMAIL03
4    |   USER04    |   EMAIL04

Table: Comments

ID   |  AUTHOR     |    COMMENT
----------------------------------
1    |   USER01    |   COMMENT...
2    |   USER02    |   COMMENT...
3    |   USER01    |   COMMENT...
4    |   USER03    |   COMMENT...

In this example the user01 have the most comments, but lets say I have to result them all with count of comments they have. And also in result I have to show users email which is stored in Users table.

How can I count and at same time check in both tables to return result? Or should I first get user info and then count ?

Upvotes: 0

Views: 160

Answers (2)

TJChambers
TJChambers

Reputation: 1499

select username,email,count(*) as cnt 
from users, comments 
where author = username 
 group by username
 order by cnt desc
 limit 1

Upvotes: 2

John Woo
John Woo

Reputation: 263723

this query below handles duplicate rows having the most number of comments,

SELECT  a.userName
FROM    Users a
        INNER JOIN Comments b
            ON a.username = b.author
GROUP BY    a.userName
HAVING  COUNT(*) = 
    (
        SELECT MAX(totalCount)
        FROM
        (
            SELECT author, COUNT(*) totalCount
            FROM    comments
            GROUP BY author
        ) a
    )

but if you want not to handle that, it can be simply done by using ORDER BY and LIMIT

SELECT  a.userName, COUNT(*) totalCount
FROM    Users a
        INNER JOIN Comments b
            ON a.username = b.author
GROUP BY    a.userName
ORDER BY totalCount DESC
LIMIT 1

Upvotes: 3

Related Questions