how to get the user in a different age groups in mysql

Question

i am having a table tb_user in which the record are as like:

Name      gender   location  grewup     date of birth

Arun       male     india    britain    1998-02-03
sonia     female     USA     dubai      1994-02-03
sheetal   female    india    Russia     1993-02-03

what i want is the record in the following manner :

Age Bracket     Total Count     Female     Male
18-25               50            22        28
26-30               30            12        18
31-36               20            10        10
37-42               10            3          7
43-48               5             2          3

i have used the query as :

select COUNT(id) as total count sum(IF(`sex` = 'Female',1,0)) as female,sum(IF(`sex` = 'Male',1,0)) as male as male FROM tb_user_info GROUP BY `date of birth`

but i am not able to get the age bracket like i mentioned

there is not proper sequence in `Age Brackets` so either we will have to hard code it, or have the same year age brackets, like 5 years or 7 years whatsoever you want.. — Deepak Sharma, Mar 09 '18 at 07:03
@DeepakSharma we can opt for proper age brackets like 18-23, 24-29 if that gives an answer — Pandat Arun, Mar 09 '18 at 07:05

score 1 · Accepted Answer · answered Mar 09 '18 at 07:46

Try this:

SELECT A.`Age Bracket`, COUNT(*) `Total Count`, SUM(IF(A.gender='female',1,0)) Female, SUM(IF(A.gender='male',1,0)) Male
FROM
(SELECT
  CASE WHEN floor(datediff(curdate(),birth_date) / 365) BETWEEN 18 AND 25 THEN '18-25'
       WHEN floor(datediff(curdate(),birth_date) / 365) BETWEEN 26 AND 30 THEN '26-30'
       WHEN floor(datediff(curdate(),birth_date) / 365) BETWEEN 31 AND 36 THEN '31-36'
       WHEN floor(datediff(curdate(),birth_date) / 365) BETWEEN 37 AND 42 THEN '37-42'
       ELSE '43-48'
  END `Age Bracket`, gender
FROM tb_user
WHERE floor(datediff(curdate(),birth_date) / 365) BETWEEN 18 AND 48) A
GROUP BY A.`Age Bracket`
ORDER BY A.`Age Bracket`;

Helpful links:

Get difference in years between two dates in MySQL as an integer

MySQL CASE Expression

MySQL IF Function

MySQL GROUP BY

MySQL ORDER BY: Sort a Result Set

how to get the user in a different age groups in mysql

1 Answers1