Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

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.
0
jackjohnson44
Asked:
jackjohnson44
  • 3
  • 3
3 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
0
 
QlemoC++ DeveloperCommented:
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

0
 
QlemoC++ DeveloperCommented:
Output as range text can be managed with
    convert(varchar(4), percent_grp) + ' - ' + convert(varchar(4), percent_grp + 0.09)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Steve WalesSenior Database AdministratorCommented:
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)

Open in new window

0
 
QlemoC++ DeveloperCommented:
sjwales,

score is now truncated twice, which does not matter for the result, but is superfluous.
0
 
Steve WalesSenior Database AdministratorCommented:
Oops!  You're right:

Joys of copy and paste :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now