I have a simple mysql table with name and age columns. I need to find the age range (say with length 5) which contains the most number of records. Please note that the range can be from anything to anything (like 1 to 5 years or 2 to 6 years). I have created a sqlfiddle for the same at http://sqlfiddle.com/#!2/a65265/1
I have tried using DIV and searched through the forums, but the closest i can get is predefined ranges like age 5-10, 10-15 etc. I need a more generic solution for all possible age ranges.
select 5 * floor((t.age-o.offset)/5) + o.offset as from_age ,5 * (floor((t.age-o.offset)/5) + 1) + o.offset - 1 as to_age ,count(*) as cnt from test as t cross join (select 0 as offset union all select 1 union all select 2 union all select 3 union all select 4) as o group by o.offset ,floor((t.age-o.offset)/5) order by cnt desc limit 1