How to group number of users by age bands in mysql

Question

Essentially I have a mysql database with users and their corresponding date of birth. I have also found the following bit of code that would help me find the users actual age from the date of birth. Finding date of birth What I need to do is find different "age bands" and count the amount of users in that age band. I have also found this example that shows exactly how to group this data. I want to calculate the age first and use it in the a way similar as shown in the following link. I have written the following code and am getting an error when running it:

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 an error stating that the field age is not known. Am I writing this incorrectly? I could easily write the whole block of code that calculates age where age is written in the case statement but this seems to be very inefficient. I am not very good at mysql (yet) and I know that there has to be a better way to do this. I guess my main question is if there is some sort of way to create a function inside a query and assign the output of that 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 first it executes the subquery and builds a table of ages, than it aggregates the age value.