Geek asks and answers

Grouping by value range in SQL

My table is as follows:

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, for example:

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

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

Partition groups would obviously have been predefined, that is, I knew in advance that my partition scheme should be: <500, 500 - 900, 900+, etc.

Thank!

+3
source share
5 answers
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 contact Dalen from the comments below and provide the output in the exact format asked 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 can improve this by using 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
+13
source

_range, - :

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

(_data ), - , :

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


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

Try:

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

run it for different value ranges.
0
source 
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
0
source

For SQL Server 2000, where we don’t have Unpivot ..... It will still only have 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
0
source

More articles:


All Articles