QUery Question - Grouping according to predefined set

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)

QUery Question - Grouping according to predefined set

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-16 : 09:26:37

Phoenix writes "I'm trying to find a way to group on a predefined set of values.

The table 'Test_Dur' has a column named 'Duration'. The requested return set will identify the number of rows in the table where 'Duration' falls into seven groups:

Less than 9
10 - 19
20 - 29
30 - 39
40 - 49
50 - 59
Greater than 59

I can do one statement for each group (e.g. SELECT COUNT(*) FROM Test_Dur WHERE Duration > 10 AND Duration <= 19)
but I'm hoping to figure out a way that doesn't require 7 seperate SELECT statements.

Anyone got a suggestion??"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 10:24:32


create table test_dur (duration int)
insert test_dur select 15
insert test_dur select 150
go

select
    sum((case when duration < 10 then 1 else 0 end)) as 'Less Than 10',
    sum((case when duration between 10 and 19 then 1 else 0 end)) as '10 - 19',
    sum((case when duration between 20 and 29 then 1 else 0 end)) as '20 - 29',
    sum((case when duration between 30 and 39 then 1 else 0 end)) as '30 - 39',
    sum((case when duration between 40 and 49 then 1 else 0 end)) as '40 - 49',
    sum((case when duration between 50 and 59 then 1 else 0 end)) as '50 - 59',
    sum((case when duration > 59 then 1 else 0 end)) as 'Greater than 59'
from
    test_dur
go

drop table test_dur
go

[edit]Apparently, when Rob and I used our powers of clairvoyance to mind-read your desired rowset, we had different visions.[/edit]

<O>

Edited by - Page47 on 07/16/2002 10:47:15

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 10:26:12

SELECT CASE
WHEN Duration < 10 THEN 'Less than 10'
WHEN Duration Between 10 AND 19 THEN '10 - 19'
WHEN Duration Between 20 AND 29 THEN '20 - 29'
WHEN Duration Between 30 AND 39 THEN '30 - 39'
WHEN Duration Between 40 AND 49 THEN '40 - 49'
WHEN Duration Between 50 AND 59 THEN '50 - 59'
ELSE 'Greater than 59' END AS Duration,
Count(*) AS Total
FROM Test_Dur
GROUP BY CASE
WHEN Duration < 10 THEN 'Less than 10'
WHEN Duration Between 10 AND 19 THEN '10 - 19'
WHEN Duration Between 20 AND 29 THEN '20 - 29'
WHEN Duration Between 30 AND 39 THEN '30 - 39'
WHEN Duration Between 40 AND 49 THEN '40 - 49'
WHEN Duration Between 50 AND 59 THEN '50 - 59'
ELSE 'Greater than 59' END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources