SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 range and count of users
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rabisco
Starting Member

15 Posts

Posted - 05/24/2013 :  15:41:09  Show Profile  Reply with Quote
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.


Edited by - rabisco on 05/24/2013 15:42:20

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/24/2013 :  15:45:06  Show Profile  Reply with Quote
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'
....
Go to Top of Page

rabisco
Starting Member

15 Posts

Posted - 05/24/2013 :  16:23:16  Show Profile  Reply with Quote
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'.


Edited by - rabisco on 05/24/2013 16:23:52
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/24/2013 :  16:25:28  Show Profile  Reply with Quote
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
Go to Top of Page

rabisco
Starting Member

15 Posts

Posted - 05/24/2013 :  16:48:06  Show Profile  Reply with Quote
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


Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/24/2013 :  16:53:57  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/24/2013 :  16:54:26  Show Profile  Reply with Quote
Remove the last line that reads "group by jp.userid"
Go to Top of Page

rabisco
Starting Member

15 Posts

Posted - 05/24/2013 :  17:18:45  Show Profile  Reply with Quote
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




Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/24/2013 :  19:53:47  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000