Return count of how many occurrences there are

I have a MySQL table that looks like this:

user_id	other_id	date
123456789	123	date1
213454678	123	date2

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

user_id	count
123324345456456576587	7
453545435343455343453	3
777676766776675654454	2
345565664545665654645	1

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:

count	times_ocurring
1	123
2	100
3	2

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.

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)
;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating