Calculating Age Groups

[rgarza]

Hi,

I need to be able to calculate age groups from a birthdate field and SUM them up. Example,

age1 = 0 - 12 years old
age2 = 13 -17 years old
age3 = 17 and above years old

Date age1 age2 age3
---- ---- ---- ----
11/04/04 3 5 14
11/03/04 15 2 13

I'm using MySQL 4.1.7 if that makes any diffrence. Any advice would be great.

Ray

Thanks,

 

[r937]

what are those Date values? where do they come from? what is the table layout of your table? what are the datatypes of the relevant columns?

rudy

http://r937.com/

SQL Consulting

 

[rgarza]

The date column is a different date, it is not the birthdate. Here is the essentials of the tables: Table1 has the statistical information with a link to Table2 via uID to get the users birthdate

Table1
uID --> VARCHAR(7)
runDate --> DATE

Table2
uID --> VARCHAR(7)
bDate --> DATE

Hope this helps.

 

[r937]

the structure of your query has to be like this --

select t1.runDate
  , sum( case when [i]age[/i] < 13
              then 1 else 0 end ) as age1
  , sum( case when [i]age[/i] 
                 between 13 and 17
              then 1 else 0 end ) as age2
  , sum( case when [i]age[/i] >= 17
              then 1 else 0 end ) as age3
  from Table1 as t1
inner
  join Table2 as t2                   
    on t1.uID = t2.uID
group
    by t1.runDate

in each case, to calculate the age you need the following expression:

year(t1.runDate) 
- year(t2.bDate) 
- case when month(t1.runDate) 
          > month(t2.bDate) then 0
       when month(t1.runDate) 
          < month(t2.bDate) then 1
       when day(t1.runDate) 
          < day(t2.bDate) then 1 
       else 0 end

rudy

http://r937.com/

SQL Consulting

 

[rgarza]

Well I was finally able to check and run the query. I thought of the code on the top but I failed to see the subsitution that could be done. I'm always amazed as to what can be done with SQL queries.

Thanks again for the help.

Ray