MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Anders Lundgren Date:September 19 2006 4:53pm
Subject:Re: Query to get count of ages
View as plain text  
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
Thread
Query to get count of agescnelson16 Sep
  • Re: Query to get count of agesJoão Cândido de Souza Neto16 Sep
    • Re: Query to get count of agesAlvaro Cobo16 Sep
    • Re: Query to get count of agescnelson18 Sep
      • Re: Query to get count of agesAlvaro Cobo18 Sep
      • Re: Query to get count of agescnelson19 Sep
  • Re: Query to get count of agesAnders Lundgren19 Sep