Count from within Range - 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 2005 Forums

Transact-SQL (2005)

Count from within Range

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2009-07-21 : 08:21:16

HI everyone,

I have a table with 2 columns
I wish to count the number of items that fit into 20 different ranges. Like 0 to 5, 5.1 to 10, 10.1 to 15....finally 95.1 to 100
and if I have no item within one of these ranges, I wish it could show 0.
Items Utilization
a            0
b            15.2
c            22.0
d            45.2
e            55.0
f            64.7
g            82.9
h            95.0
i            100.0

I made the following but when there is no tiem within a range, it doesn't show 0 for this range. So I wish to have 20 results becasue there are 20 ranges.


select RangeValue, Count(Items) As Nbr_Items
FROM
(
SELECT CASE 
WHEN [Utilization] BETWEEN 0.0 AND 5.0 THEN '0 to 5'
WHEN [Utilization] BETWEEN 5.1 AND 10.0 THEN ' 5.1 to 10'
WHEN [Utilization] BETWEEN 10.1 AND 15.0 THEN '10.1 to 15'
WHEN [Utilization] BETWEEN 15.1 AND 20.0 THEN '15.1 to 20'
WHEN [Utilization] BETWEEN 20.1 AND 25.0 THEN '20.1 to 25'
WHEN [Utilization] BETWEEN 25.1 AND 30.0 THEN '25.1 to 30'
WHEN [Utilization] BETWEEN 30.1 AND 35.0 THEN '30.1 to 35'
WHEN [Utilization] BETWEEN 35.1 AND 40.0 THEN '35.1 to 40'
WHEN [Utilization] BETWEEN 40.1 AND 45.0 THEN '40.1 to 45'
WHEN [Utilization] BETWEEN 45.1 AND 50.0 THEN '45.1 to 50'
WHEN [Utilization] BETWEEN 50.1 AND 55.0 THEN '50.1 to 55'
WHEN [Utilization] BETWEEN 55.1 AND 60.0 THEN '55.1 to 60'
WHEN [Utilization] BETWEEN 60.1 AND 65.0 THEN '60.1 to 65'
WHEN [Utilization] BETWEEN 65.1 AND 70.0 THEN '65.1 to 70'
WHEN [Utilization] BETWEEN 70.1 AND 75.0 THEN '70.1 to 75'
WHEN [Utilization] BETWEEN 75.1 AND 80.0 THEN '75.1 to 80'
WHEN [Utilization] BETWEEN 80.1 AND 85.0 THEN '80.1 to 85'
WHEN [Utilization] BETWEEN 85.1 AND 90.0 THEN '85.1 to 90'
WHEN [Utilization] BETWEEN 90.1 AND 95.0 THEN '90.1 to 95'
WHEN [Utilization] BETWEEN 95.1 AND 100 THEN '95.1 to 100'
END as [RangeValue],  Items
FROM #Temp
) a
GROUP BY RangeValue

Thanks fo any help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 08:24:57

It should be

select RangeValue, Count(Items) As Nbr_Items
FROM
(
SELECT
CASE
WHEN [Utilization] BETWEEN 0.0 AND 5.0 THEN '0 to 5'
END as [0 to 5],
CASE
WHEN [Utilization] BETWEEN 5.1 AND 10.0 THEN ' 5.1 to 10'
END as [5.1 to 10],
.
.
.
Items
FROM #Temp
) a
GROUP BY RangeValue

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-07-21 : 09:47:03

Hi Madhivanan,

I did not mention this, but i wish to have the results into two columns table only. Can you help me?
like:
RangeValue Nbr_Items
0 to 5          6
5.1 to 10     15
10.1 to 15    44
.                  .
.                  .
95.1 to 100   3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 10:12:28

Something like


select ranges.RangeValue, Count(a.RangeValue) As Nbr_Items
FROM 
(
	select '0 to 5' as RangeValue union all
	select '5.1 to 10' as RangeValue union all
	select '10.1 to 15' as RangeValue union all
	.
	.
	.
	select '95.1 to 100' as RangeValue 
) as ranges
left join
(
SELECT CASE 
WHEN [Utilization] BETWEEN 0.0 AND 5.0 THEN '0 to 5'
WHEN [Utilization] BETWEEN 5.1 AND 10.0 THEN '5.1 to 10'
WHEN [Utilization] BETWEEN 10.1 AND 15.0 THEN '10.1 to 15'
WHEN [Utilization] BETWEEN 15.1 AND 20.0 THEN '15.1 to 20'
WHEN [Utilization] BETWEEN 20.1 AND 25.0 THEN '20.1 to 25'
WHEN [Utilization] BETWEEN 25.1 AND 30.0 THEN '25.1 to 30'
WHEN [Utilization] BETWEEN 30.1 AND 35.0 THEN '30.1 to 35'
WHEN [Utilization] BETWEEN 35.1 AND 40.0 THEN '35.1 to 40'
WHEN [Utilization] BETWEEN 40.1 AND 45.0 THEN '40.1 to 45'
WHEN [Utilization] BETWEEN 45.1 AND 50.0 THEN '45.1 to 50'
WHEN [Utilization] BETWEEN 50.1 AND 55.0 THEN '50.1 to 55'
WHEN [Utilization] BETWEEN 55.1 AND 60.0 THEN '55.1 to 60'
WHEN [Utilization] BETWEEN 60.1 AND 65.0 THEN '60.1 to 65'
WHEN [Utilization] BETWEEN 65.1 AND 70.0 THEN '65.1 to 70'
WHEN [Utilization] BETWEEN 70.1 AND 75.0 THEN '70.1 to 75'
WHEN [Utilization] BETWEEN 75.1 AND 80.0 THEN '75.1 to 80'
WHEN [Utilization] BETWEEN 80.1 AND 85.0 THEN '80.1 to 85'
WHEN [Utilization] BETWEEN 85.1 AND 90.0 THEN '85.1 to 90'
WHEN [Utilization] BETWEEN 90.1 AND 95.0 THEN '90.1 to 95'
WHEN [Utilization] BETWEEN 95.1 AND 100 THEN '95.1 to 100'
END as [RangeValue]
FROM (select 8 as [Utilization] union all select 52) Temp
) a on ranges.Rangevalue=a.Rangevalue
GROUP BY ranges.RangeValue

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-07-21 : 10:32:37

That did it Madhivanan, I modified the end a bit like this
FROM #Temp
) a on ranges.Rangevalue=a.Rangevalue
GROUP BY ranges.RangeValue
order by left(ranges.RangeValue,2)

results:
Range Value Nbr_Items
00 to 5 0
05.1 to 10 0
10.1 to 15 1
15.1 to 20 0
20.1 to 25 3
25.1 to 30 2
30.1 to 35 4
35.1 to 40 9
40.1 to 45 13
45.1 to 50 8
50.1 to 55 9
55.1 to 60 13
60.1 to 65 15
65.1 to 70 18
70.1 to 75 20
75.1 to 80 32
80.1 to 90 0
90.1 to 95 13
95.1 to 100 9

I thank you very much for helping me ! :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-22 : 02:11:58

quote:
Originally posted by infodemers

That did it Madhivanan, I modified the end a bit like this
FROM #Temp
) a on ranges.Rangevalue=a.Rangevalue
GROUP BY ranges.RangeValue
order by left(ranges.RangeValue,2)

results:
Range Value Nbr_Items
00 to 5 0
05.1 to 10 0
10.1 to 15 1
15.1 to 20 0
20.1 to 25 3
25.1 to 30 2
30.1 to 35 4
35.1 to 40 9
40.1 to 45 13
45.1 to 50 8
50.1 to 55 9
55.1 to 60 13
60.1 to 65 15
65.1 to 70 18
70.1 to 75 20
75.1 to 80 32
80.1 to 90 0
90.1 to 95 13
95.1 to 100 9

I thank you very much for helping me ! :-)

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources