dividing data into buckets - 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 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]

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

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]

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]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-06-28 : 03:53:15

Thanks all especially Khtan it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources