MySQL - Group by range

Question

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 1

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.

Answer 2

select t.myvalue as [range], count(*) as [occurences]
from (
  select myvalue,
   case when myvalue >= 0 and myvalue< 10 then '0-9'
   when myvalue >= 10 and myvalue< 20 then '10-19'
   when myvalue >= 20 and myvalue< 20 then '20-29'
   when myvalue >= 30 and myvalue< 40 then '30-39'
   else '40+' end as range
 from t) t
group by t.myvalue