range and count of users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

range and count of users

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources