How to select the count of values grouped by ranges

Question

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?

Answer 1

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)

Answer 2

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

Answer 3

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