[ACCEPTED]-Grouping by a range of values in SQL-sql-server
Accepted answer
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 4 below and provide output in the exact format 3 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 2 2005+, you could improve on this by leveraging 1 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 3 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 2 table (_data on my example) to get something 1 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
0
Try:
select count(*) from TABLE where VALUE_FIELD between VALUE1 and VALUE2
run it for different value ranges.
0
For SQL Server 2000, where we do not have 2 Unpivot ..... It will still have only one 1 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
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.