This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
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.
I want to group on this column, but in .1 pt range, meaning group 0-.09, .1-.19, .2-.29.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I used this as an example:
Still tweaking on making it display the range you want as a literal
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)
Still tweaking on making it display the range you want as a literal
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:
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
Output as range text can be managed with
convert(varchar(4), percent_grp) + ' - ' + convert(varchar(4), percent_grp + 0.09)
convert(varchar(4), percent_grp) + ' - ' + convert(varchar(4), percent_grp + 0.09)
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
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial