Group by age range

Question

I would like to tabulate customers registered in a database mysql by age range.

I have a field nascimento guy date.

With the consultation below I can group by birth.

SELECT COUNT(*) AS qtd, YEAR(FROM_DAYS(TO_DAYS(NOW())-
TO_DAYS(nascimento))) AS idade FROM cliente GROUP BY nascimento ASC ORDER BY qtd DESC

But I return the amount only to the exact age of group by, would like an interval count (example below):

30 - 20~25 anos
20 - 25~30 anos
10 - 30~35 anos

How to adjust the above query so that it presents this type of answer?

Answer 1

To do this you need to create a "case" to group the results according to the age range you want. Here’s an example:

select 
case 
when T2.idade between 0 and 19 then 'Ate 19' 
when T2.idade between 20 and 25 then 'De 20 a 25' 
when T2.idade between 26 and 30 then 'De 26 a 30' 
when T2.idade between 31 and 35 then 'De 31 a 35' 
when T2.idade between 36 and 120 then 'De 36 a 120' 
end as faixas, sum(T2.total) as total
from ( SELECT YEAR(now()) - YEAR(nascimento) - ( DAYOFYEAR(now()) < DAYOFYEAR(nascimento)) as idade, count(*) as total 
from cliente  
group by idade) T2 
group by faixas