Grouping the occurance of value in sets.

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  September 12, 2008 at 7:58 am

  #193478

  I have a table where I want to group the ID in sets of 0-20,21-40,41-60,60-80,81-100 depending upon the value of ID which comes between these ranges.

  create table temp (Id int)

  insert into temp select 19

  insert into temp select 45

  insert into temp select 84

  insert into temp select 0

  insert into temp select 41

  insert into temp select 26

  insert into temp select 99

  insert into temp select 20

  insert into temp select 50

  insert into temp select 10

  select * from temp

  The output should be like this.

  Group count

  0-20 4

  21-40 1

  41-60 3

  60-80 0

  81-100 2

  drop table temp

  How can this be done?

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 12, 2008 at 8:23 am

  #871008

  create table ranges(rmin int, rmax int)

  insert into ranges(rmin , rmax )

  select 0,20 union all

  select 21,40 union all

  select 41,60 union all

  select 61,80 union all

  select 81,100

  select cast(rmin as varchar(10))+'-'+cast(rmax as varchar(10)) as [Group],

  count(*) as [count]

  from ranges

  left outer join temp on Id between rmin and rmax

  group by rmin,rmax

  order by rmin

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537