CODEWITHSUNDEEP
mysqllaraveleloquentaggregate-functions
Henson
Henson

Reputation: 5913

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

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.

Upvotes: 1

Views: 47

Answers (2)

Henson
Henson

Reputation: 5913

If anyone is looking for a solution in Laravel, this is what I end up with

$posts = DB::query()
    ->fromSub(function ($query) {
        $query->from('posts')
            ->selectRaw('user_id, COUNT(*) as total_posts')
            ->groupBy('user_id');
    }, 't')
    ->selectRaw('total_posts, COUNT(*) number_of_users')
    ->groupBy('total_posts')
    ->orderBy('total_posts', 'asc')
    ->get();

Upvotes: 1

forpas
forpas

Reputation: 164099

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.

Upvotes: 1

Related Questions