Grouping by a range of values in SQL

Question

My table is as following:

id | label_id | value
1  | 1        | 500
2  | 1        | 600
3  | 1        | 900
4  | 1        | 10000
5  | 1        | 300
6  | 2        | ...
....................

Now, I want to generate a query result such as:

caption        | count
Less than 500  | 1
500 to 900     | 2
Above 900      | 1

In short, I'm trying to partition the result set so it is grouped by a range of values. Any ideas?

The partition groups obviously would be pre-defined, meaning I'd know ahead of time that my partition scheme should be: < 500, 500 - 900, 900+ and so forth.

Thank you!

Answer 1

select sum(case when value < 500 then 1 else 0 end) as [less than 500],
       sum(case when value >= 500 and value <= 900 then 1 else 0 end) as [500 to 900],
       sum(case when value > 900 then 1 else 0 end) as [above 900]
    from YourTable

EDIT: To address Dalen's concern from the comments below and provide output in the exact format given in the question:

select 'less than 500' as Caption, count(*) as Count
    from YourTable
    where value < 500
union all
select '500 to 900' as Caption, count(*) as Count
    from YourTable
    where value >= 500 and value <= 900
union all
select 'above 900' as Caption, count(*) as Count
    from YourTable
    where value > 900

And, for SQL Server 2005+, you could improve on this by leveraging UNPIVOT with my original query:

select Caption, Count
    from (select sum(case when value < 500 then 1 else 0 end) as [less than 500],
                 sum(case when value >= 500 and value <= 900 then 1 else 0 end) as [500 to 900],
                 sum(case when value > 900 then 1 else 0 end) as [above 900]
              from YourTable) t
unpivot (Count for Caption in ([less than 500], [500 to 900], [above 900])) p