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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With