I have a MySQL table that looks like this:

Here, user_id is a multikey and is re-occurring for some entries. Date is irrelevant for this task. I tried this query:

select user_id, count(user_id) as count from exp_pixel_data group by user_id;

And this returned

This result tells me how often a user_id is occurring in the table. This may be a good start, but now i need how often this counts are occurring in the result of the last query. So the question is, how many user_ids occur 7 times in the table? I need a SQL query which returns something like this:

and so on.

This means that 123 times there are user_ids that occur one time in the main table, 100 times user_ids that occur 2 Times and 2 times user_ids that occur 3 times.

CodePudding user response：

Is it possible you're trying to do this (count the counts)?

Fiddle

SELECT COUNT(xcount) AS count_of_counts
     , xcount
  FROM (SELECT user_id
             , COUNT(user_id) AS xcount
          FROM exp_pixel_data
         GROUP BY user_id
       ) xxx
 GROUP BY xcount
 ORDER BY COUNT(xcount)
;

and with MySQL, we can use the derived column name in the ORDER BY:

SELECT COUNT(xcount) AS count_of_counts
     , xcount
  FROM (SELECT user_id
             , COUNT(user_id) AS xcount
          FROM exp_pixel_data
         GROUP BY user_id
       ) xxx
 GROUP BY xcount
 ORDER BY count_of_counts
;

Result (given test data below):

 ----------------- -------- 
| count_of_counts | xcount |
 ----------------- -------- 
|               1 |      5 |
|               2 |      2 |
|               2 |      1 |
 ----------------- -------- 

Setup:

CREATE TABLE exp_pixel_data (user_id   int, val  int default 0);

INSERT INTO exp_pixel_data (user_id) VALUES
    (12345)
  , (12345)
  , (12399)
  , (12399)
  , (12388)
  , (12377)
  , (12355)
  , (12355)
  , (12355)
  , (12355)
  , (12355)
;