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