Tips for calculating the value of a specific segment in SQL

I am helping customers with some statistics today. I need to make statistics based on a specific area. So I searched the internet and found no satisfactory results, the closest is the http://bbs.csdn.net/topics/70184424 7 floor of the answer, but this statistics can not specify the segment, so I figured out, wrote such a statement:

Declare @ tb table (id int identity (1, 1), num int)

Insert into @ tb (num) values (1), (10), (20), (25), (12), (15), (13), (22 ), (5), (50), (80), (110)

Select count (*), overall, over10, over20, over50, over100 from (select 1 as overall, convert (bit, num/10) as over10, convert (bit, num/20) as over20, convert (bit, num/50) as over50, convert (bit, num/100) as over100 from @ tb) as a group by overall, over10, over20, over50, over100

I perform a ratio based on each specified segment and convert it to the bit type. In this way, the result is either 1 that meets the condition or 0 that does not meet the condition, then you can get the result in the entire group.

Of course, I didn't include the statistical section in SQL, So I modified the SQL command.

Declare @ tb table (id int identity (1, 1), num int)

Insert into @ tb (num) values (1), (10), (20), (25), (12), (15), (13), (22 ), (5), (50), (80), (110)

Select N 'scope '= (case (overall + over10 + over20 + over50 + over100) when 1 then '0-9' when 2 then '10-19 'when 3 then' 20-49 'when 4 then' 50-99 'else' 100 + 'end ), count (*) from (select 1 as overall, convert (bit, num/10) as over10, convert (bit, num/20) as over20, convert (bit, num/50) as over50, convert (bit, num/100) as over100 from @ tb) as a group by (overall + over10 + over20 + over50 + over100)

In this way, we can get the Section statistics we need.