1

Here is a link to a very similar question, except in their example they only are concerned about one column, where I will be concerned about more than one. SQL Select most common values

Here is my problem:

I need to display an email addr, number of comments that user has left, and display the most common username associated with each email. I have already taken care of the first two steps; i.e. I have it displaying email along with number of comments. Now the client wants the most common username.

here is the query to get the the email and count of comments I mentioned above.

select comment_author_email, count(*) from wp_comments group by comment_author_email order by count(*) desc

Now I have the task of getting the most common username to display in the third column.

For example:

email  |  username | comment
------------------------------
1@test | one       | blah..
1@test | ONE       | blah..
1@test | one       | blah..
2@test | TWO       | blah..
2@test | TWO       | blah..
3@test | tre       | blah..

I would expect as output

email   |numComments| mostCommonName
------------------------------------
1@test  | 3         | one
2@test  | 2         | TWO
3@test  | 1         | tre
Community
  • 1
  • 1
Brandon 10x
  • 43
  • 4

2 Answers2

2

This is a good place to use the group_concat()/substring_index() trick. But this requires a subquery. 

select comment_author_email, sum(cnt),
       substring_index(group_concat(username order by cnt desc), ',', 1) as MostCommonName
from (select comment_author_email, username, count(*) as cnt
      from wp_comments
      group by comment_author_email, username
     ) cu
group by comment_author_email
order by sum(cnt) desc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Here is another way to do this. (It's easy to extract the most frequent username by applying GROUP BY on emails+usernames ordered by frequency):

select email, sum(n) numComments, username mostCommonName
from (
  select email, username, count(comment) n, count(username) u
  from wp_comments
  group by email, username
  order by u desc) a
group by email;

fiddle

Fabricator
  • 12,722
  • 2
  • 27
  • 40