Get the most active users from an 'actions' table

Question

I have a table for actions conducted by my users, the 'email' column in that table specifies which user did what. What I want to do is run a query to find the most active user based on the amount of actions containing his email. That is, I want the query to return the email that is most present in my table. I've hit a wall trying to figure out how to do this, any idea?

ID  EMAIL   ACTION
1   tester@gmail.com    deletion
2   tester@gmail.com    addition
3   tester@gmail.com    modification
4   tester@gmail.com    deletion
5   not_a_tester@gmail.com    deletion
6   someone_else@gmail.com    deletion
7   another_guy@gmail.com    deletion
8   not_a_tester@gmail.com    deletion

Should return tester@gmail.com in this instance.

In this case, GROUP BY returned the email of someone with two actions, who also happens to be the first ID on the database ,as opposed to someone else who has 3. — Idan Elhalwani, Apr 10 '17 at 12:02
http://stackoverflow.com/questions/14249689/select-id-having-maximum-count-of-id — inarilo, Apr 10 '17 at 12:05
@GordonLinoff pls check out the duplicate link and hammer if you think it is appropriate — Shadow, Apr 10 '17 at 12:10

score 0 · Answer 1 · edited Apr 10 '17 at 13:14

0

select count(email) AS maxcount,email 
from table_name 
group by email
having MAX(count(email))

edited Apr 10 '17 at 13:14

ʰᵈˑ

11,279
3
26
49

answered Apr 10 '17 at 13:07

Elias

47
7

Code-only answers are discouraged. Please *explain* what you have done. – ʰᵈˑ Apr 10 '17 at 13:14
What i have simply done is to group the emails and get the count. The return the most frequent email in the having clause. – Elias Apr 10 '17 at 13:28
put it in your answer, not as a comment to your answer. – ʰᵈˑ Apr 10 '17 at 13:31

Get the most active users from an 'actions' table

1 Answers1