0

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?

  • 2
    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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.