Group by number of occurrences based on the result of a group by query-CodePudding

Imagine a table posts like this

posts
---

id
user_id
title

If I want to print out each user with the total number of posts, I might do this

SELECT user_id, COUNT(*) as total_posts FROM posts GROUP BY user_id

The result will be something like this:

user_id | total_posts
=====================
1       | 5
2       | 2
5       | 3
8       | 3

Now, what if I want to group by the total_posts? So I'm looking for a result like this:

total_posts | number_of_users
=============================
5       | 1
2       | 1
3       | 2

Is it possible to do this via MySQL? I'm solving this currently by using Laravel's collection, but it requires mapping through every row of the user_id | total_posts table, which can be huge, thus can be taxing to memory.

CodePudding user response：

You need 2 levels of aggregation:

SELECT total_posts,
       COUNT(*) number_of_users
FROM (
  SELECT user_id, 
         COUNT(*) total_posts 
  FROM posts 
  GROUP BY user_id
) t
GROUP BY total_posts
ORDER BY total_posts DESC;

or, for MySql 8.0 :

SELECT DISTINCT 
       COUNT(*) total_posts,
       COUNT(*) OVER (PARTITION BY COUNT(*)) number_of_users
FROM posts 
GROUP BY user_id
ORDER BY total_posts DESC;

See a simplified demo.