How to count items in specified ranges in one query? I have this table:

Id | Username | FollowersCount  
 1 | user1    | 1500
 2 | user2    | 700000
 3 | user3    | 94000
 4 | user4    | 117000


I want to find the number of users who have less than 50k followers, users who have between 50k and 100k, users who have between 100k and 150k, and so on.

Of course I can write many queries to get the result. But is there a way to somehow mix aggregate functions with between clause?
This will return a row for every range from 0-49999 up to whatever range the highest followers count falls in.

DECLARE @RangeSize int = 50000;
DECLARE @NumberOfRanges int = (SELECT MAX(FollowersCount) FROM #table) / @RangeSize;

;WITH n(n) AS
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n &lt;= @NumberOfRanges
), ranges(s,e) AS
(
  SELECT @RangeSize * (n-1), @RangeSize * n FROM n
)
SELECT r.s, r.e-1, COUNT(t.Id)
FROM ranges AS r
LEFT OUTER JOIN #table AS t
ON t.FollowersCount &gt;= r.s
AND t.FollowersCount &lt; r.e
GROUP BY r.s, r.e
ORDER BY r.s;


If you only want the ranges with a non-zero user count, just change the LEFT OUTER to INNER. And I prefer an open-ended range over BETWEEN for two reasons - one, it's ambiguous, since if someone has exactly 100K, do they fall in the 50k - 100k bucket, or the 100k - 150k bucket, and two, while you can force that either way, it doesn't translate well to other data types, where it's even more troublesome.