How to group number of users by age groups in mysql

Question

Essentially, I have a mysql database with users and their corresponding date of birth. I also found the following bit of code that helps me find the actual age of users from the date of birth. Searching for a date of birth I need to find different "age groups" and count the number of users in this age group. I also found this example that shows exactly how to group this data. I want to calculate age first and use it in the same way as shown in the following link . I wrote the following code and get an error message at startup:

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(data_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(data_of_birth, '00-%m-%d')) AS age, COUNT(*), CASE WHEN age >= 10 AND age <= 20 THEN '10-20' WHEN age >=21 AND age <=30 THEN '21-30' WHEN age >=31 AND age <=40 THEN '31-40' WHEN age >=41 AND age <= 50 THEN '31-40' WHEN age >=51 AND age <=60 THEN '51-60' WHEN age >=61 THEN '61+' END AS ageband .. .. GROUP BY ageband 

I get a message that the age of the field is unknown. Am I writing this incorrectly? I could easily write an entire block of code that calculates the age when age is written in the case, but it seems very inefficient. I am not very good at mysql (yet), and I know that there should be a better way to do this. I assume my main question is, is there a way to create a function inside a query and assign the output of this function to a value?

Answer 1

In this case, you can use a subquery:

 SELECT COUNT(*), CASE WHEN age >=10 AND age <=20 THEN '10-20' WHEN age >=21 AND age <=30 THEN '21-30' WHEN age >=31 AND age <=40 THEN '31-40' WHEN age >=41 AND age <=50 THEN '41-50' WHEN age >=51 AND age <=60 THEN '51-60' WHEN age >=61 THEN '61+' END AS ageband FROM ( DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS age, .. .. ) as tbl GROUP BY ageband; 

So, he first performs a subquery and builds an age table, which aggregates the age value.