How to choose the number of values ​​grouped by range

Question

Greetings, Stack!

I need to select the number of values ​​grouped by ranges.

To illustrate, suppose I have the following values ​​in a columm table: 1,2,4,5,6,8,9,11,13,16

Then, I want to retreave their number 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) 

Etc...

How to do this in a request?

Answer 1

Perhaps 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; 

In your example, 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) 

Answer 2

One way is the + case sum 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 populated with:

 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 

Answer 3

Calculate the value you can group. In this case, you just need to divide the value by 5 to get the result:

 select value / 5 as Group, count(*) as Cnt from TheTable group by value / 5 

This will give you the result:

 Group Cnt 0 3 1 4 2 2 3 1 

Answer 4

 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 

Answer 5

What 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 allow you to get lines containing the information displayed in the lines =)