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 2000 Forums
 SQL Server Development (2000)
 dividing data into buckets

Author  Topic 

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-06-26 : 11:17:14
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
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-26 : 11:25:54
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
Posting Yak Master

101 Posts

Posted - 2009-06-26 : 11:43:09
I need distinct users


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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 23:03:06
[code]
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]
[/code]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 23:08:26
[code]
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
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-06-28 : 03:53:15
Thanks all especially Khtan it worked.
Go to Top of Page
   

- Advertisement -