desbest desbest - 6 months ago 11
SQL Question

How do I group by the count value in sql

Say I have


SELECT *, count(userid) FROM stars GROUP by userid


How do I change the
GROUP by userid
to group by the
count(userid)


I searched google but couldn't find anything.

For those stuck: I want to count how many users have X amount of stars.

Answer

Use two levels of group by:

select cnt, count(*), min(userid), max(userid)
from (select userid, count(*) as cnt
      from stars
      group by userid
     ) u
group by cnt
order by cnt;

I call this type of query a "histogram of histograms" query. I include the min() and max() values because I often find those useful for further investigation.