# 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?

• `SELECT .. SUM(CASE WHEN FollowersCount BETWEEN lower AND upper THEN 1 ELSE 0 END) .. GROUP BY NULL`. – Akina Nov 24 '18 at 16:46
• I didn't understand this @Akina. Could you please explain? – Saeed Neamati Nov 24 '18 at 17:06
• `SUM(CASE(` works with a finite number of ranges, but it turns into spaghetti after 3 or 4 IMHO. – Aaron Bertrand Nov 24 '18 at 18:28

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 <= @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 >= r.s
AND t.FollowersCount < 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.