Count number of Users with more then 3 posts

Question

I have 2 tables Users and Posts and I need only number of users who have more than 3 posts and who have more than 5 posts.

I need something like this:

NumberOfUsers	Posts
555	>3
888	>5

SELECT 
    COUNT( u.Id)
FROM
    Users u
INNER JOIN Posts p ON (u.Id=p.OwnerId)
HAVING COUNT(p.Id)>3

I try this but having count does not work I think.

Answer 1

You can use two levels of aggregation

select sum(case when cnt > 3 then 1 else 0 end) cnt_3, 
       sum(case when cnt > 5 then 1 else 0 end) cnt_5
from (select ownerid, count(*) cnt from posts group by ownerid) p

Note that you don't need to bring in the users table ; the posts table has all the information we need.

Depending on your database, syntax shortcuts might be available, as in MySQL :

select sum(cnt > 3) cnt_3, 
       sum(cnt > 5) cnt_5
from (select ownerid, count(*) cnt from posts group by ownerid) p

Or in Posgres :

select count(*) where(cnt > 3) cnt_3, 
       count(*) where(cnt > 5) cnt_5
from (select ownerid, count(*) cnt from posts group by ownerid) p