CODEWITHSUNDEEP
mysql
Mark Robinson
Mark Robinson

Reputation: 1619

MySQL Frequency of frequency report

Given many users have many posts

I have a table of posts that has a foreign key user_id

I want to generate a report that shows the frequency of users against frequency of posts

e.g.

3 users wrote 2 posts each

2 users wrote 1 post each

1 user wrote 4 posts

Number of users | Number of posts
--------------- | ------------------
1               | 4
2               | 1
3               | 2

My attempt:

SELECT inner_table.frequency_posts, 
       Count(*) AS frequency_users
FROM   posts 
       INNER JOIN (SELECT user_id, 
                          Count(*) AS frequency_posts 
                   FROM   posts
                   GROUP  BY user_id) AS inner_table 
               ON posts.user_id = inner_table.user_id
GROUP  BY inner_table.frequency_posts

I think frequency_posts is working but counting frequency_users isn't giving the right values - when I look at the inner select on it's own and manually add up the posts I don't get the same values

Upvotes: 0

Views: 46

Answers (1)

Palindromer
Palindromer

Reputation: 974

You have to use Group by twice:

SELECT     
  COUNT(*) AS NumberOfUsers,
  foo.NumberOfPosts
FROM
    (SELECT 
        p.UserId AS UserId, 
        COUNT(*) AS NumberOfPosts
    FROM
        posts AS p
    GROUP BY UserId) as foo
GROUP BY foo.NumberOfPosts

Upvotes: 1

Related Questions