CODEWITHSUNDEEP
mysqlsqlselectcount
kabuto178
kabuto178

Reputation: 3167

Query for multiple count values

SELECT  cm.commenter_id,
        cm.comment,
        m.id,
        (
            SELECT COUNT(*) AS r_count
            FROM comments
            GROUP BY comments.commenter_id
        ) AS count,
        m.display_name
FROM    comments cm
        INNER JOIN members m
            ON cm.commenter_id = m.id

From this query I want to get the display_name for the person with the highest count of comments. Any guidance is appreciated.

Upvotes: 5

Views: 162

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

I think the simplest way is just to sort your query and take the first row:

SELECT  cm.commenter_id,
        cm.comment,
        m.id,
        (
            SELECT COUNT(*) AS r_count
            FROM comments
            GROUP BY comments.commenter_id
        ) AS count,
        m.display_name
FROM    comments cm
        INNER JOIN members m
            ON cm.commenter_id = m.id
order by count desc
limit 1

Upvotes: 0

John Woo
John Woo

Reputation: 263893

SELECT  m.id, m.display_name, COUNT(*) totalComments
FROM    comments cm
        INNER JOIN members m
            ON cm.commenter_id = m.id
GROUP   BY m.id, m.display_name
HAVING  COUNT(*) =
    (
        SELECT  COUNT(*) totalCount
        FROM    Comments
        GROUP   BY  commenter_id
        ORDER   BY totalCount DESC
        LIMIT 1
    )

Upvotes: 3

Related Questions