Hail, Stack!
I need to select the count of values grouped by ranges.
To exemplify, suppose that I have the following values in a table columm: 1,2,4,5,6,8,9,11,13,16
Then, I want to retreave the count of them in ranges of 5, like this:
From 0 to 4 there is 3 values (1,2,4)
From 5 to 9 there is 4 values (5,6,8,9)
From 10 to 14 there is 2 values (11,13)
From 15 to 19 there is 1 values (16)
And so on...
How can I make this in a query?
Maybe this is what you want:
SELECT
5 * (n div 5) as 'from',
5 * (n div 5) + 4 as 'to',
COUNT(*)
FROM yourtable
GROUP BY n div 5;
For your sample this query gives you
+------+------+----------+
| from | to | count(*) |
+------+------+----------+
| 0 | 4 | 3 |
| 5 | 9 | 4 |
| 10 | 14 | 2 |
| 15 | 19 | 1 |
+------+------+----------+
4 rows in set (0.00 sec)
One way is the sum + case approach:
select sum(case when col1 between 0 and 4 then 1 end)
, sum(case when col1 between 5 and 9 then 1 end)
, sum(case when col1 between 10 and 14 then 1 end)
...
from YourTable
Another approach is to have a range table, filled like:
start end
0 4
5 9
10 14
Then you can:
select r.start
, r.end
, count(case when yt.col1 between r.start and r.end then 1 end)
from YourTable yt
cross join
RangeTable r
group by
r.start
, r.end
Calculate a value that you can group on. In this case you just have to divide the value by 5 to get that result:
select value / 5 as Group, count(*) as Cnt
from TheTable
group by value / 5
This will give you are result like this:
Group Cnt
0 3
1 4
2 2
3 1
select
val / 5 as grp,
case val / 5
when 0 then ' 0 to 5'
when 1 then ' 5 to 10'
when 2 then '10 to 15'
when 3 then '15 to 20'
end
as grpname,
count(distinct val) as cnt
from
(
select 1 as val
union select 2
union select 4
union select 5
union select 6
union select 8
union select 9
union select 11
union select 13
union select 16
) a
group by
val / 5
How about
for(i=MIN_OF_TABLE-1;i<=MAX_OF_TABLE;$i+=RANGE){
SELECT COUNT(`VALUE`),GROUP_CONCAT(`VALUE`) FROM `TABLE` WHERE `VALUE`>i AND `VALUE`<=i+RANGE;
}
This will fetch rows containing the information you displayed in rows =)
