mister.magoo (5/24/2013)

---

You are grouping by userid, so of course you get one row per userid....

Try this:

select

x.point_range

, count(jp.userid) as countofusers

from StatusLevelPnt jp

inner join user ju on jp.userid = ju.userid

cross apply

(

SELECT

case

when sum(jp.points) between 0 and 1 then '0-1'

when sum(jp.points) between 1 and 2 then '1-2'

when sum(jp.points) between 2 and 50 then '2-50'

when sum(jp.points) between 51 and 100 then '51-100'

when sum(jp.points) between 101 and 300 then '101-300'

when sum(jp.points) between 301 and 1000 then '301-1000'

when sum(jp.points) between 1001 and 1500 then '1001-1500'

when sum(jp.points) between 1501 and 2000 then '1501-2000'

when sum(jp.points) between 2001 and 3000 then '2001-3000'

when sum(jp.points) between 3001 and 4000 then '3001-4000'

when sum(jp.points) between 4001 and 5000 then '4001-5000'

when sum(jp.points) between 5001 and 6000 then '5001-6000'

when sum(jp.points) between 6001 and 7000 then '6001-7000'

when sum(jp.points) between 7001 and 8000 then '7001-8000'

when sum(jp.points) between 8001 and 9000 then '8001-9000'

when sum(jp.points) between 9001 and 10000 then '9001-10000'

when sum(jp.points) > 10000 then '10000-above'

end as point_range

) x

group by x.point_range

Note: I used a cross apply just to avoid repeating the huge CASE statement in the "group by"