How to display ranges in output

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

Transact-SQL (2000)

How to display ranges in output

Author

Topic

Varun
Starting Member

9 Posts

Posted - 2006-07-04 : 05:27:25

I have the following table:

Name Age
A 15
B 18
C 22
D 27
E 33
F 42

I need to have the following output:
Range Count
0-10 0
11-20 2
21-30 2
31-40 1
41-50 1

Could anyone let me know how to achieve this in SQL Server 2005?

Thanks,
Varun

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 05:52:08

It is hard to do, since the ranges are not equally distributed, and there is no data for all intervals.
But here is the first approach

-- prepare test data
declare @test table (Name varchar, age tinyint)

insert @test
select 'A', 15 union all
select 'B', 18 union all
select 'C', 22 union all
select 'D', 27 union all
select 'E', 33 union all
select 'F', 42

-- do the work
select * from @test

select		convert(varchar, 10 * (age / 10)) + '-' + convert(varchar, 9 + 10 * (age / 10)) Range,
		count(*) Count
from		@test
group by	convert(varchar, 10 * (age / 10)) + '-' + convert(varchar, 9 + 10 * (age / 10))

Here is the second approach

-- do the work again thus semi-hardwired
SELECT		z.Range,
		COUNT(t.Age) Count
FROM		(
			SELECT	0 FromNum, 10 ToNum, '0-10' Range UNION ALL
			SELECT	11, 20, '11-20' Range UNION ALL
			SELECT	21, 30, '21-30' Range UNION ALL
			SELECT	31, 40, '31-40' Range UNION ALL
			SELECT	41, 50, '41-50'
		) z
LEFT JOIN	@test t on t.age between z.fromnum and z.tonum
group by	z.range

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-07-04 : 06:58:10

Wouldn't a user defined function do it?

If values lie between this and this insert it into that column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 07:01:44

quote:
Originally posted by drewsalem

Wouldn't a user defined function do it?

If values lie between this and this insert it into that column.

Show us!

Peter Larsson
Helsingborg, Sweden

Varun
Starting Member

9 Posts

Posted - 2006-07-04 : 07:08:39

Thanks Peter!

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-07-04 : 07:26:09

Sorry guys. I have a deadline at the end of the day today, and as you might see from my other posts, I'm not dead hot (yet) in SQL.

With out looking it up, maybe something like...

quote:
Name Age
A 15
B 18
C 22
D 27
E 33
F 42

I need to have the following output:
Range Count
0-10 0
11-20 2
21-30 2
31-40 1
41-50 1

Create Function Varuns_Function (par1)
Returns Integer
Begin
If par1 between 0 and 10 
then insert par1 into NewTable.Column0-10
Else if Par1 between 11 and 20
Then Insert par 1 into newtable.column21-30

and so on...

End If
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources