Mysql - Count how many rows have a certain number?

Question

Hello, I have a query that checks a tables and groups all entries from a user and counts those entries

  SELECT username, 
         count(userid)  
    FROM orders 
GROUP BY userid

This returns a list of username's and how many orders they have submitted

username  count(userid)
------------------------
test      1  
test2     1  
test3     3  
test4     3  
test4     3  
test4     3  

What i want the query to do is count how many users have X orders and return that number.

So from the above results users that have 1 order should be 2 and users that have 3 orders should be 4. So on and so forth can this be done?

Answer 1

I'd think you could just add something like this around your query:

SELECT InnerCount, COUNT(*)
FROM
(
    -- your query
    SELECT username, count(userid) AS InnerCount
    FROM orders
    GROUP BY userid
) t1
GROUP BY InnerCount

(Caveat: haven't touched mysql in years)

Answer 2

Use:

SELECT COUNT(*)
  FROM (SELECT o.username, 
               COUNT(o.userid) AS cnt
          FROM ORDERS o
      GROUP BY o.userid
        HAVING cnt = ?) x

Replace "?" with the number you want to see the number of users with that count value.