Ravikant yadav Ravikant yadav - 4 months ago 8
SQL Question

Query to groupby records by age based on date

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

Answer

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
Comments