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 2000 Forums
 SQL Server Development (2000)
 dividing data into buckets
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 06/26/2009 :  11:17:14  Show Profile  Reply with Quote
Hi,

I have a table which stores information of application used by users in no of days.

create table #temp
(
application varchar(100),
no_of_days varchar(100),
username varchar(100)
)


insert into #temp values('abc','2','rock')
insert into #temp values('abc','2','rock')
insert into #temp values('abc','3','jill')
insert into #temp values('abc','4','jack')
insert into #temp values('abc','1','mac')
insert into #temp values('abc','8','jose')
insert into #temp values('abc','9','mark')
insert into #temp values('abc','10','shreyas')
insert into #temp values('xyz','11','julie')
insert into #temp values('xyz','12','lucie')
insert into #temp values('xyz','13','laura')
insert into #temp values('xyz','1','oscar')
insert into #temp values('xyz','19','oscar')
insert into #temp values('efg','20','oscar')
insert into #temp values('efg','10','ali')
insert into #temp values('efg','6','ali')
insert into #temp values('efg','7','hitesh')
insert into #temp values('efg','5','hitesh')


I am using this queries

select application, count(distinct username) from #temp
where no_of_days between 1 and 10
group by application

select application, count(distinct username) from #temp
where no_of_days between 11 and 20
group by application

my ouptut should be

-------------------------------------
application 1-10days 11-20 days
--------------------------------------
abc 7
efg 2 1
xyz 1 4
-----------------------------------------

I want to make a single query to display above output.

Kindly help

Regards,
Sachin

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 06/26/2009 :  11:25:54  Show Profile  Reply with Quote
select [application]
,sum(case when no_of_days <= 10 then 1 else 0 end) as [1-10 days]
,sum(case when no_of_days >= 11 then 1 else 0 end) as [11-20 days]
from #temp
group by [application]
Go to Top of Page

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 06/26/2009 :  11:43:09  Show Profile  Reply with Quote
I need distinct users


your query gives be abc 8 for range 1-10 which should be 7
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 06/26/2009 :  23:03:06  Show Profile  Reply with Quote

try this small modification for vijay query
select [application],sum(seq1) AS [1-10 days],sum(seq2) AS [11-20 days]
from (select distinct [application],username
,(case when no_of_days <= 10 then 1 else 0 end) as seq1
,(case when no_of_days >= 11 then 1 else 0 end) as seq2
from #temp)s
group by [application]
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17593 Posts

Posted - 06/26/2009 :  23:08:26  Show Profile  Reply with Quote

select	application, 
	count(distinct case when no_of_days between  1 and 10 then username end),
	count(distinct case when no_of_days between 11 and 20 then username end)
from	#temp
group by application



KH
Time is always against us

Go to Top of Page

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 06/28/2009 :  03:53:15  Show Profile  Reply with Quote
Thanks all especially Khtan it worked.
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.08 seconds. Powered By: Snitz Forums 2000