If I follow you correctly, you can use two levels of aggregation. The following query puts each bucket in a separate row:
select
case
when cnt < 10 then '< 10'
when cnt < 20 then '10-20'
else '> 20'
end as bucket,
count(*) cnt
from (
select count(p.owneruserid) cnt
from users u
left join posts p on p.owneruserid = u.id
group by u.id
) t
group by case
when cnt < 10 then '< 10'
when cnt < 20 then '10-20'
else '> 20'
end
Or you can get all three counts on the same row as follows:
select
sum(case when cnt < 10 then 1 else 0 end) as cnt_less_than_10,
sum(case when cnt >= 10 and cnt < 20 then 1 else 0 end) as cnt_10_to_20,
sum(case when cnt > 20 then 1 else 0 end) as cnt_more_than_20
from (
select count(*) cnt
from users u
inner join posts p on p.owneruserid = u.id
group by u.id
) t
CLICK HERE to find out more related problems solutions.