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"
I get this error when I run your query...
Msg 1015, Level 15, State 1, Line 1
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.