Server version: 5.0.24
This query is working OK for me. I'm using FROM_DAYS() to avoid errors
due to leap years.
SELECT extract(YEAR FROM from_days(datediff(curdate(), dateofbirth))) AS
age, SUM(extract(YEAR FROM from_days(datediff(curdate(), birth_date))))
AS total_age FROM user_info GROUP BY age;
Or if you want the total age of all combined append "WITH ROLLUP" like this:
SELECT extract(YEAR FROM from_days(datediff(curdate(), dateofbirth))) AS
age, SUM(extract(YEAR FROM from_days(datediff(curdate(), dateofbirth))))
AS total_age FROM user_info GROUP BY age WITH ROLLUP;
Regards,
Anders
c nelson wrote:
I'm working on MySQL v5.0 and I have a table with dateOfBirth and I want
a histogram of ages at a point in time. I tried something like:
select round(datediff(curdate(), dateofbirth)/365) as age, count(age)
from myTable group by age;
but MySQL Query Browser says:
Unknown column 'age' in 'field list' 1054
What am I doing wrong? How can I count ages?
--
Anders Lundgren
Viba IT Handelsbolag
Web: http://www.vibait.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]