'Group by age ranges in MySQL

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

mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Zantafio

Related Questions