thelost thelost - 5 months ago 27
SQL Question

MySQL - Group by range

I need to count the records within value ranges.

For example: for the set

1, 7, 9, 23, 33, 35, 1017


select count(myvalue) group by round(myvalue / 10)
gives something like:

0-10 -> 3
10-20 -> 0
20-30 -> 1
30-40 -> 2
1010-1020 -> 1


This works fine. However, I need to set an upper limit, so that MySQL returns
40+ --> 1
?
How can this be achieved ?

Answer

You can either sum the values on the client side or use two queries, possibly with union, to fetch the data, e.g.:

select round(myvalue / 10), count(myvalue) from table where myvalue < 40 group by round(myvalue / 10)
union
select '40+', count(myvalue) from table where myvalue >= 40

It is absolutely possible to write it in a single query with subqueries or convoluted conditions but it just wouldn't be as simple and maintainable.