Status: Solved
Priority: Medium
Security: Public
Views: 463
Last Modified: 2012-12-23

sql group on number range, not exact number

I have a column with a decimal which is a percentage .76, .24, 1.

I want to group on this column, but in .1 pt range, meaning group 0-.09, .1-.19, .2-.29.

jackjohnson44

Asked: 2012-12-22

Advertise Here

3 Solutions

LVL 23

Steve WalesSenior Database AdministratorCommented: 2012-12-22

I used this as an example:

use demo
go
create table tab1 (student char(20), score decimal(9,2))

insert into tab1 values ('Fred', .74)
insert into tab1 values ('Mary', .72)
insert into tab1 values ('Ann', .69)
insert into tab1 values ('George', .62)
insert into tab1 values ('Mark', .76)
insert into tab1 values ('Bob', .54)

select round(score, 1, 1), count(*)
from tab1
group by round(score, 1, 1)

                                        
--------------------------------------- -----------
0.50                                    1
0.60                                    2
0.70                                    3

(3 row(s) affected)

Open in new window

Still tweaking on making it display the range you want as a literal

LVL 73

QlemoBatchelor, Developer and EE Topic AdvisorCommented: 2012-12-22

You can group by an expression, so adding
convert(int, percent*10)/10.0
to your group by should do. But since it is usually required to see the group itself in the output, it's better to use an in-place table:

select a,b, percent_grp, count(*)
from (
  select a, b, convert(int, percent*10)/10.0 as percent_grp
  from tbl
) x
group by a, b, percent_grp

Open in new window

LVL 73

QlemoBatchelor, Developer and EE Topic AdvisorCommented: 2012-12-22

Output as range text can be managed with
convert(varchar(4), percent_grp) + ' - ' + convert(varchar(4), percent_grp + 0.09)

LVL 23

Steve WalesSenior Database AdministratorCommented: 2012-12-22

Here, this would do it and show you the range in the output.

Inner select does the grouping (and so can use indexes since you're not putting functions in the group bys and what not which might invalidate them) and then the outer select does the formatting of that output

select convert(char(4), round(score, 1, 1))+'-'+convert(char(4), round(score, 1, 1)+0.09) as score_range, total
from
(
select round(score, 1, 1) as score, count(*) as total
from tab1
group by round(score, 1, 1)
) as x
order by score_range

score_range total
----------- -----------
0.50-0.59   1
0.60-0.69   2
0.70-0.79   3

(3 row(s) affected)