Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


range and count of users


range and count of users

Author
Message
rabisco
rabisco
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

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 ....


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 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 countofusers
from StatusLevelPnt jp
inner join user ju on jp.userid = ju.userid
group by jp.userid




Returns the following result...


Range userid
2-50 3
2-50 4
2-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
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

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 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"

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • rabisco
    rabisco
    SSC Journeyman
    SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

    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 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.
    Evil Kraig F
    Evil Kraig F
    SSCertifiable
    SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

    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 Farrell

    Never 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 Netiquette
    For index/tuning help, follow these directions. |Tally Tables

    Twitter: @AnyWayDBA
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    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 grouped
    group by point_range



    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • rabisco
    rabisco
    SSC Journeyman
    SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

    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 ALL
    SELECT'1001','2005','10', UNION ALL
    SELECT'1002','2005','10', UNION ALL
    SELECT'1003','2002','10', UNION ALL
    SELECT'1004','1','10', UNION ALL
    SELECT'1005','1','10', UNION ALL
    SELECT'1006','2002','10', UNION ALL
    SELECT'1007','2002','10', UNION ALL
    SELECT'1008','1','10', UNION ALL
    SELECT'1009','1','10', UNION ALL
    SELECT'1010','1','10', UNION ALL
    SELECT'1011','1','10', UNION ALL
    SELECT'1012','1','10', UNION ALL
    SELECT'1013','1','10', UNION ALL
    SELECT'1014','1','10', UNION ALL
    SELECT'1015','1','10', UNION ALL
    SELECT'1016','1','10', UNION ALL
    SELECT'1017','1','10', UNION ALL
    SELECT'1018','1','10', UNION ALL
    SELECT'1019','1','10', UNION ALL
    SELECT'1020','2003','10', UNION ALL
    SELECT'1021','2002','10', UNION ALL
    SELECT'1022','2002','10', UNION ALL
    SELECT'1023','2005','10', UNION ALL
    SELECT'1024','2005','10', UNION ALL
    SELECT'1025','2005','10', UNION ALL
    SELECT'1026','2005','10', UNION ALL
    SELECT'1027','2005','10', UNION ALL
    SELECT'1028','2005','10', UNION ALL
    SELECT'1029','2005','10', UNION ALL
    SELECT'1030','2005','10', UNION ALL
    SELECT'1031','2005','10', UNION ALL
    SELECT'1032','2005','10', UNION ALL
    SELECT'1033','2005','10', UNION ALL
    SELECT'1034','2005','10', UNION ALL
    SELECT'1035','2005','10', UNION ALL
    SELECT'1036','2005','10', UNION ALL
    SELECT'1037','2005','10', UNION ALL
    SELECT'1038','2005','10', UNION ALL
    SELECT'1039','2005','10', UNION ALL
    SELECT'1040','2005','10', UNION ALL
    SELECT'1041','2005','10', UNION ALL
    SELECT'1042','2005','10', UNION ALL
    SELECT'1043','2005','10', UNION ALL
    SELECT'1044','2005','10', UNION ALL
    SELECT'1045','2005','10', UNION ALL
    SELECT'1046','2005','10', UNION ALL
    SELECT'1047','2005','10', UNION ALL
    SELECT'1048','2005','10', UNION ALL
    SELECT'1049','2005','10', UNION ALL
    SELECT'1050','2005','10', UNION ALL
    SELECT'1051','2005','10', UNION ALL
    SELECT'1052','2005','10', UNION ALL
    SELECT'1053','2005','10', UNION ALL
    SELECT'1054','2005','10', UNION ALL
    SELECT'1055','2005','10', UNION ALL
    SELECT'1056','2005','10', UNION ALL
    SELECT'1057','1','10', UNION ALL
    SELECT'1058','2005','10', UNION ALL
    SELECT'1059','2005','10', UNION ALL
    SELECT'1060','2005','10', UNION ALL
    SELECT'1061','2005','10', UNION ALL
    SELECT'1062','2005','10', UNION ALL
    SELECT'1063','2005','10', UNION ALL
    SELECT'1064','2005','10', UNION ALL
    SELECT'1065','2005','10', UNION ALL
    SELECT'1066','2005','10', UNION ALL
    SELECT'1067','2005','10', UNION ALL
    SELECT'1068','2005','10', UNION ALL
    SELECT'1069','2002','10', UNION ALL
    SELECT'1070','2002','10', UNION ALL
    SELECT'1071','2002','10', UNION ALL
    SELECT'1072','2003','10', UNION ALL
    SELECT'1073','2002','10', UNION ALL
    SELECT'1074','2002','10', UNION ALL
    SELECT'1075','2002','10', UNION ALL
    SELECT'1077','2005','10', UNION ALL
    SELECT'1078','2005','10', UNION ALL
    SELECT'1079','2005','10', UNION ALL
    SELECT'1080','2009','10', UNION ALL
    SELECT'1081','2005','10', UNION ALL
    SELECT'1082','2002','10', UNION ALL
    SELECT'1083','2002','15', UNION ALL
    SELECT'1084','2005','10', UNION ALL
    SELECT'1085','2005','10', UNION ALL
    SELECT'1086','2005','10', UNION ALL
    SELECT'1087','2005','10', UNION ALL
    SELECT'1088','2005','10', UNION ALL
    SELECT'1089','2008','10', UNION ALL
    SELECT'1090','2008','10', UNION ALL
    SELECT'1091','2008','10', UNION ALL
    SELECT'1092','2008','10', UNION ALL
    SELECT'1093','2017','15', UNION ALL
    SELECT'1095','2005','10', UNION ALL
    SELECT'1096','2005','10', UNION ALL
    SELECT'1097','2005','10', UNION ALL
    SELECT'1098','2005','10', UNION ALL
    SELECT'1099','2005','10', UNION ALL
    SELECT'1100','2005','10', UNION ALL
    SELECT'1101','2005','10', UNION ALL




    What I'm expecting is




    Rangeof points Numberofuserids
    0-1 40
    2-50 20


    rabisco
    rabisco
    SSC Journeyman
    SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

    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 grouped
    group by point_range



    mister.magoo, spot on. Just what I was looking for.

    Thanks!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search