CODEWITHSUNDEEP
mysqlsql
Mariano
Mariano

Reputation: 1073

Get the sum() of a count(*)

I have a MySQL table:

Everytime a user does some action is done, I insert a record. I want to retrieve with one SQL sentence the number of users and how many actions they have done. I want a summary of how many users have 0 actions, 1 action, 2 actions, etc... My reasoning is to group by username to get how many actions each user has, then sum those that have the same number of actions.

SELECT count(*), SUM(count(*)) 
from actions 
group by username 
order by SUM(count(*)) desc

This doesn't work of course but says more or less what I want to do. Is this possible?

Upvotes: 0

Views: 75

Answers (2)

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

I want a summary of how many users have 0 actions, 1 action, 2 actions, etc...

SELECT nbOfActions, COUNT(1) nbOfUsers
FROM
    (SELECT COUNT(1) nbOfActions, username 
     FROM actions
    GROUP BY username) x
GROUP BY nbOfActions

You can add ORDER BY to outer query to if you need:

ORDER BY COUNT(1) DESC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Try this:

select numactions, count(*), min(username), max(username)
from (select username, count(*) as numactions
      from actions
      group by username
     ) a
group by numactions
order by numactions;

Upvotes: 2

Related Questions