Group by age ranges in MySQL

Question

We have a date of birth field(type date) and user_id in the table and we want to get count of users group by age based on the date of birth field

For e.g. after running we get following records

Age  Total_Users 
1-20    50
20-30   100
30-50   500
50-100  600

how we to write this type of query? Please suggest. Currently I am managing via php code but it is taking too much time due to lots of records Thanks

How do your raw data look like? please post some sample data — 1000111, Jul 21 '16 at 11:42
Look at the correct, not the accepted, answer to this question. http://stackoverflow.com/questions/2533890/how-to-get-an-age-from-a-d-o-b-field-in-mysql . Then use GROUP BY on the computed ages. — O. Jones, Jul 21 '16 at 11:47

1000111 · Accepted Answer · 2016-07-21T14:15:30.173

May be a query like below would do the job.

SELECT 
CASE WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 20 THEN '1-20'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 30 THEN '20-30'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 50 THEN '30-50'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 50 THEN '50-100' END AS age,
COUNT(*) total
FROM dob_table
GROUP BY age;

WORKING DEMO

Input:

| id |                 birth_date |
|----|----------------------------|
|  1 | February, 01 2014 00:00:00 |
|  2 | February, 01 2014 00:00:00 |
|  3 | February, 01 2014 00:00:00 |
|  4 | February, 01 2010 00:00:00 |
|  5 | February, 27 1989 00:00:00 |
|  6 | February, 27 1989 00:00:00 |
|  7 | February, 27 1989 00:00:00 |
|  8 | February, 27 1989 00:00:00 |
|  9 | February, 27 1989 00:00:00 |

Output:

age      total
1-20        4
20-30       5

Hii , Your Query worked for me but there is only problem that it is not working with limit if we pass limit like 0,10 or etc.. — Ravikant yadav, Jul 22 '16 at 13:40

score 0 · Answer 2 · answered Apr 19 '22 at 10:33

there's a simpler way:

SELECT 
CASE WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 20 THEN '0-20'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 30 THEN '20-30'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 40 THEN '30-40'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 50 THEN '30-40'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 60 THEN '40-50'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 70 THEN '50-60'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 80 THEN '60-70'
    WHEN (TIMESTAMPDIFF(YEAR, dob, CURDATE())) <= 110 THEN '70+' END AS age,
COUNT(*) total
FROM duo
GROUP BY age;

Group by age ranges in MySQL

2 Answers2