| Author |
Topic |
|
rabisco
Starting Member
15 Posts |
 Posted - 2013-05-24 : 15:41:09
|
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.
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-24 : 15:45:06
|
Like this. SELECT
SUM(CASE WHEN jp.points BETWEEN 0 AND 1 THEN 1 ELSE 0 END) AS '0-1',
SUM(CASE WHEN jp.points BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS '1-2',
SUM(CASE WHEN jp.points BETWEEN 2 AND 50 THEN 1 ELSE 0 END) AS '2-50'
.... |
 |
|
|
rabisco
Starting Member
15 Posts |
Posted - 2013-05-24 : 16:23:16
|
quote:
Originally posted by James K
Like this. SELECT
SUM(CASE WHEN jp.points BETWEEN 0 AND 1 THEN 1 ELSE 0 END) AS '0-1',
SUM(CASE WHEN jp.points BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS '1-2',
SUM(CASE WHEN jp.points BETWEEN 2 AND 50 THEN 1 ELSE 0 END) AS '2-50'
....
I ran this ....
select
sum (case when jp.points between 0 and 1 then 1 else 0 end) as '0-1'
end as point_range
from StatusLevelPnt jp
inner join user ju on userid = ju.userid
group by jp.userid
But I get the error ...
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
|
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-24 : 16:25:28
|
You have an extra "end as point_range in there"select
sum (case when jp.points between 0 and 1 then 1 else 0 end) as '0-1'
end as point_range
from StatusLevelPnt jp
inner join user ju on userid = ju.userid
group by jp.userid
|
|
|
|
rabisco
Starting Member
15 Posts |
Posted - 2013-05-24 : 16:48:06
|
quote:
Originally posted by James K
You have an extra "end as point_range in there"select
sum (case when jp.points between 0 and 1 then 1 else 0 end) as '0-1'
end as point_range
from StatusLevelPnt jp
inner join user ju on userid = ju.userid
group by jp.userid
Thanks, but this does not give me what I need. It returns a row for eveery user rather then the total number of users who for whhom the sum of their points is 0 to 1.
As a background the following query returns sum of points for every userid....
select jp.userid, sum(jp.points) as points
from StatusLevelPnt jp
inner join user ju on jp.userid = ju.userid
group by jp.userid
order by jp.userid
i.e. userid sumof points
1234 1
2345 1
So what I'm looking for is....
range of points number of userdid
0 to 1 2
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-24 : 16:53:57
|
quote:
Originally posted by rabisco
Thanks, but this does not give me what I need.
Show the full query where you used James' code. His solution should work. If it isn't giving expected results, show sample data and full table DDL. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-24 : 16:54:26
|
| Remove the last line that reads "group by jp.userid" |
|
|
|
rabisco
Starting Member
15 Posts |
Posted - 2013-05-24 : 17:18:45
|
quote:
Originally posted by James K
Remove the last line that reads "group by jp.userid"
This is the full query...
select
sum(case when jp.points between 0 and 1 then 1 else 0 end) as '0-1',
sum(case when jp.points between 1 and 2 then 2 else 1 end)as '1-2',
sum(case when jp.points between 2 and 50 then 50 else 2 end) as '2-50'
from StatusLevelPnt jp
inner join user ju on jp.userid = ju.userid
Now that I have removed the "group by jp.userid', I get this...
0-1 1-2 2-50
269980 699920 8888226
Which is not right since the total number of userids is 146,000.
This the DDL for the StatusLevelPnt table
CREATE TABLE [dbo].[StatusLevelPnt](
[pointID] [bigint] NOT NULL,
[userID] [bigint] NOT NULL,
[points] [bigint] NOT NULL,
)
Sample data
1000 2005 10
1001 2005 10
1002 2005 10
1003 2002 10
1004 1 10
1005 1 10
1006 2002 10
1007 2002 10
1008 1 10
1009 1 10
1010 1 10
1011 1 10
1012 1 10
1013 1 10
1014 1 10
1015 1 10
1016 1 10
1017 1 10
1018 1 10
1019 1 10
1020 2003 10
1021 2002 10
1022 2002 10
1023 2005 10
1024 2005 10
1025 2005 10
1026 2005 10
1027 2005 10
1028 2005 10
1029 2005 10
1030 2005 10
1031 2005 10
1032 2005 10
1033 2005 10
1034 2005 10
1035 2005 10
1036 2005 10
1037 2005 10
1038 2005 10
1039 2005 10
1040 2005 10
1041 2005 10
1042 2005 10
1043 2005 10
1044 2005 10
1045 2005 10
1046 2005 10
1047 2005 10
1048 2005 10
1049 2005 10
1050 2005 10
1051 2005 10
1052 2005 10
1053 2005 10
1054 2005 10
1055 2005 10
1056 2005 10
1057 1 10
1058 2005 10
1059 2005 10
1060 2005 10
1061 2005 10
1062 2005 10
1063 2005 10
1064 2005 10
1065 2005 10
1066 2005 10
1067 2005 10
1068 2005 10
1069 2002 10
1070 2002 10
1071 2002 10
1072 2003 10
1073 2002 10
1074 2002 10
1075 2002 10
1077 2005 10
1078 2005 10
1079 2005 10
1080 2009 10
1081 2005 10
1082 2002 10
1083 2002 15
1084 2005 10
1085 2005 10
1086 2005 10
1087 2005 10
1088 2005 10
1089 2008 10
1090 2008 10
1091 2008 10
1092 2008 10
1093 2017 15
1095 2005 10
1096 2005 10
1097 2005 10
1098 2005 10
1099 2005 10
1100 2005 10
1101 2005 10
|
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-24 : 19:53:47
|
What is the output you are looking for? Copy and paste the code and run it. It has two queries in there - see if either of those give you what you are looking for.create table #tmp (userid int, points int);
insert into #tmp values (1,10),(2,1),(3,1),(4,0),(5,0),(6,1);
SELECT
SUM(CASE WHEN jp.points >= 0 and jp.points < 1 THEN 1 ELSE 0 END) AS '0-1',
SUM(CASE WHEN jp.points >= 1 and jp.points < 2 THEN 1 ELSE 0 END) AS '1-2',
SUM(CASE WHEN jp.points >= 2 and jp.points < 50 THEN 1 ELSE 0 END) AS '2-50'
from
#tmp jp;
select * from
(
SELECT
SUM(CASE WHEN jp.points >= 0 and jp.points < 1 THEN 1 ELSE 0 END) AS '0-1',
SUM(CASE WHEN jp.points >= 1 and jp.points < 2 THEN 1 ELSE 0 END) AS '1-2',
SUM(CASE WHEN jp.points >= 2 and jp.points < 50 THEN 1 ELSE 0 END) AS '2-50'
from
#tmp jp
) s
unpivot (usercount for range in ([0-1],[1-2],[2-50]))U
drop table #tmp;
|
|
|
|
|
range and count of users