Grouping by a range of values in SQL

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 9开发者_Python百科00     | 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!

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

i would introduce another table called _range, something like that:

label     |lower|upper  |
-------------------------
up to 500 |0    |500    |
500 to 1k |501  |1000   |
over 1k   |1001 |1000000|

the you can join it with your table (_data on my example) to get something like you asked:

SELECT _range.label,COUNT(*) AS count
FROM _range JOIN _data
   ON value >= lower and value <= upper
GROUP BY _range.label

result will be

label     |count|
-----------------
up to 500 |2    |
500 to 1k |2    |
over 1k   |1    |

select case when  value < 500 then 'Less than 500'
                when value >= 500 and value <= 900 then '500 - 900'
                else 'Above 900' end as caption, COUNT(*) as count
    from mytable
    group by case when  value < 500 then 'Less than 500'
                when value >= 500 and value <= 900 then '500 - 900'
                else 'Above 900' end

Try:

select count(*) from TABLE where VALUE_FIELD between VALUE1 and VALUE2

run it for different value ranges.

For SQL Server 2000, where we do not have Unpivot ..... It will still have only one scan ..

SELECT 

 CASE WHEN T1.Group = 1 Then 'less than 500'
      WHEN T1.Group = 2 Then '500 to 900'
      WHEN T1.Group = 3 Then 'above 900' END as [Label],

 CASE WHEN T1.Group = 1 Then T.[less than 500]
      WHEN T1.Group = 2 Then T.[500 to 900]
      WHEN T1.Group = 3 Then T.[above 900] END as [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
CROSS JOIN 
(
   Select 1 as Group
    UNION ALL
   Select 2
    UNION ALL
   Select 3
) T1