## range and count of users

 Author Message rabisco SSC Journeyman Group: General Forum Members Points: 76 Visits: 235 I have a table which has 2 columns i.e. userid and points. I'm attemtping to get the number of userid in ranges of points i.e. 0 to 1, 2 to 50 etc.The following query ....`selectcase 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 2001 and 3000 then '5001-6000'when sum(jp.points) between 3001 and 4000 then '6001-7000'when sum(jp.points) between 4001 and 5000 then '7001-8000'when sum(jp.points) between 2001 and 3000 then '8001-9000'when sum(jp.points) between 3001 and 4000 then '9001-10000'when sum(jp.points) > 10000 then '10000-above'end as point_range, count(jp.userid) as countofusersfrom StatusLevelPnt jpinner join user ju on jp.userid = ju.useridgroup by jp.userid`Returns the following result...`Range userid 2-50 32-50 42-50 1`What i'm looking for is the count of userids who have points in range 2 to 50 etc. ..... `Range userid 2-50 7` mister.magoo SSCrazy Group: General Forum Members Points: 2278 Visits: 7827 You are grouping by userid, so of course you get one row per userid....Try this:`select x.point_range , count(jp.userid) as countofusersfrom StatusLevelPnt jpinner join user ju on jp.userid = ju.useridcross 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 ) xgroup by x.point_range`Note: I used a cross apply just to avoid repeating the huge CASE statement in the "group by" MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw rabisco SSC Journeyman Group: General Forum Members Points: 76 Visits: 235 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 countofusersfrom StatusLevelPnt jpinner join user ju on jp.userid = ju.useridcross 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 ) xgroup 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 1An 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. Evil Kraig F SSCertifiable Group: General Forum Members Points: 5715 Visits: 7660 Without sample schema and data we're trying to offer you ideas, not perfect error free code.If you would like us to be able to provide solutions that are (nearly) guaranteed to work, help us volunteers help you. You'll find what we prefer when we try to give code assistance in the first link in my signature. It includes a #schema for your table, some sample test data, and expected results from the sample data. Otherwise it'll simply be best effort. - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA mister.magoo SSCrazy Group: General Forum Members Points: 2278 Visits: 7827 Sorry, that's the problem with not having sample data to test with...you probable want something more like this:`select point_range,count(*) from ( select jp.userid , 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 from StatusLevelPnt jp inner join user ju on jp.userid = ju.userid group by jp.userid) as groupedgroup by point_range` MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw rabisco SSC Journeyman Group: General Forum Members Points: 76 Visits: 235 Evil Kraig F (5/24/2013)Without sample schema and data we're trying to offer you ideas, not perfect error free code.If you would like us to be able to provide solutions that are (nearly) guaranteed to work, help us volunteers help you. You'll find what we prefer when we try to give code assistance in the first link in my signature. It includes a #schema for your table, some sample test data, and expected results from the sample data. Otherwise it'll simply be best effort.Thanks. Table schema`CREATE TABLE [dbo].[StatusLevelPnt]( [pointID] [bigint] NOT NULL, [userID] [bigint] NOT NULL, [points] [bigint] NOT NULL, )`Sampe data (StatusLevelPnt table)`SELECT'1000','2005','10', UNION ALLSELECT'1001','2005','10', UNION ALLSELECT'1002','2005','10', UNION ALLSELECT'1003','2002','10', UNION ALLSELECT'1004','1','10', UNION ALLSELECT'1005','1','10', UNION ALLSELECT'1006','2002','10', UNION ALLSELECT'1007','2002','10', UNION ALLSELECT'1008','1','10', UNION ALLSELECT'1009','1','10', UNION ALLSELECT'1010','1','10', UNION ALLSELECT'1011','1','10', UNION ALLSELECT'1012','1','10', UNION ALLSELECT'1013','1','10', UNION ALLSELECT'1014','1','10', UNION ALLSELECT'1015','1','10', UNION ALLSELECT'1016','1','10', UNION ALLSELECT'1017','1','10', UNION ALLSELECT'1018','1','10', UNION ALLSELECT'1019','1','10', UNION ALLSELECT'1020','2003','10', UNION ALLSELECT'1021','2002','10', UNION ALLSELECT'1022','2002','10', UNION ALLSELECT'1023','2005','10', UNION ALLSELECT'1024','2005','10', UNION ALLSELECT'1025','2005','10', UNION ALLSELECT'1026','2005','10', UNION ALLSELECT'1027','2005','10', UNION ALLSELECT'1028','2005','10', UNION ALLSELECT'1029','2005','10', UNION ALLSELECT'1030','2005','10', UNION ALLSELECT'1031','2005','10', UNION ALLSELECT'1032','2005','10', UNION ALLSELECT'1033','2005','10', UNION ALLSELECT'1034','2005','10', UNION ALLSELECT'1035','2005','10', UNION ALLSELECT'1036','2005','10', UNION ALLSELECT'1037','2005','10', UNION ALLSELECT'1038','2005','10', UNION ALLSELECT'1039','2005','10', UNION ALLSELECT'1040','2005','10', UNION ALLSELECT'1041','2005','10', UNION ALLSELECT'1042','2005','10', UNION ALLSELECT'1043','2005','10', UNION ALLSELECT'1044','2005','10', UNION ALLSELECT'1045','2005','10', UNION ALLSELECT'1046','2005','10', UNION ALLSELECT'1047','2005','10', UNION ALLSELECT'1048','2005','10', UNION ALLSELECT'1049','2005','10', UNION ALLSELECT'1050','2005','10', UNION ALLSELECT'1051','2005','10', UNION ALLSELECT'1052','2005','10', UNION ALLSELECT'1053','2005','10', UNION ALLSELECT'1054','2005','10', UNION ALLSELECT'1055','2005','10', UNION ALLSELECT'1056','2005','10', UNION ALLSELECT'1057','1','10', UNION ALLSELECT'1058','2005','10', UNION ALLSELECT'1059','2005','10', UNION ALLSELECT'1060','2005','10', UNION ALLSELECT'1061','2005','10', UNION ALLSELECT'1062','2005','10', UNION ALLSELECT'1063','2005','10', UNION ALLSELECT'1064','2005','10', UNION ALLSELECT'1065','2005','10', UNION ALLSELECT'1066','2005','10', UNION ALLSELECT'1067','2005','10', UNION ALLSELECT'1068','2005','10', UNION ALLSELECT'1069','2002','10', UNION ALLSELECT'1070','2002','10', UNION ALLSELECT'1071','2002','10', UNION ALLSELECT'1072','2003','10', UNION ALLSELECT'1073','2002','10', UNION ALLSELECT'1074','2002','10', UNION ALLSELECT'1075','2002','10', UNION ALLSELECT'1077','2005','10', UNION ALLSELECT'1078','2005','10', UNION ALLSELECT'1079','2005','10', UNION ALLSELECT'1080','2009','10', UNION ALLSELECT'1081','2005','10', UNION ALLSELECT'1082','2002','10', UNION ALLSELECT'1083','2002','15', UNION ALLSELECT'1084','2005','10', UNION ALLSELECT'1085','2005','10', UNION ALLSELECT'1086','2005','10', UNION ALLSELECT'1087','2005','10', UNION ALLSELECT'1088','2005','10', UNION ALLSELECT'1089','2008','10', UNION ALLSELECT'1090','2008','10', UNION ALLSELECT'1091','2008','10', UNION ALLSELECT'1092','2008','10', UNION ALLSELECT'1093','2017','15', UNION ALLSELECT'1095','2005','10', UNION ALLSELECT'1096','2005','10', UNION ALLSELECT'1097','2005','10', UNION ALLSELECT'1098','2005','10', UNION ALLSELECT'1099','2005','10', UNION ALLSELECT'1100','2005','10', UNION ALLSELECT'1101','2005','10', UNION ALL`What I'm expecting is`Rangeof points Numberofuserids0-1 402-50 20` rabisco SSC Journeyman Group: General Forum Members Points: 76 Visits: 235 mister.magoo (5/24/2013)Sorry, that's the problem with not having sample data to test with...you probable want something more like this:`select point_range,count(*) from ( select jp.userid , 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 from StatusLevelPnt jp inner join user ju on jp.userid = ju.userid group by jp.userid) as groupedgroup by point_range`mister.magoo, spot on. Just what I was looking for.Thanks!