Query for multiple count values

Question

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.

John Woo · Accepted Answer · 2013-01-30 15:00:24Z

3

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
    )

edited Jan 30, 2013 at 15:00

answered Jan 30, 2013 at 14:54

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kabuto178 Over a year ago

mind explaining HAVING COUNT(*) line to me? never really used it like that before

John Woo Over a year ago

HAVING COUNT(*) works like a WHERE clause. But the main difference is that HAVING supports aggregate functions just like MAX, MIN, SUM, etc.. while WHERE won't.

kabuto178 Over a year ago

Thanks, also this works within this scope, but I should have posted more of the query which it is nested in. Throwing an error now

kabuto178 Over a year ago

Oh nevermind, it was my mistake had left out a column reference. Thanks again @JW.

Gordon Linoff · Accepted Answer · 2013-01-30 18:36:47Z

0

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

answered Jan 30, 2013 at 18:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Query for multiple count values

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related