desbest desbest - 2 years ago 80
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

I searched google but couldn't find anything.

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download