Subscribe via RSS

Sql Get Age From Date of Birth

Share and Enjoy:
  • Print
  • email
  • Digg
  • DZone
  • Twitter
  • Facebook
  • del.icio.us
  • Google Bookmarks
  • Reddit
  • Slashdot
  • Technorati
  • Yahoo! Buzz

Today I searched mysql get age from date of birth. I was looking get some demographic data from my database’s user table. I found a good starting query from java2s.com which returns each users age in a table called bird.

SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth))
- (RIGHT(CURDATE(),5)
AS age
FROM Bird;

I modified the query to get the average age.

SELECT avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))) as averageAge
FROM user_info;

I also wanted to see the average age for each sex.

SELECT sex, avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))) as averageAge
FROM user_info
group by sex

Then i wanted to see the percentage of male and female in the database.

SELECT sex, (100 * count(sex)/(
                    select count(sex) from user_info
           )) as percentSex
FROM user_info
group by sex

Also i wanted to see the percentages for a certain age group

// XX replace with age range you are looking for
SELECT (100 * count(birthday)/(
                   select count(birthday) from user_info
           )) as percentage
FROM user_info
where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX

I wanted to see what percentages for male and female were for a certain age group.

// XX replace with age range you are looking for
SELECT sex, (100 * count(birthday)/(
           select count(birthday) from user_info where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX
      )) as percentSex
FROM user_info
where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX
group by sex

This data will be extremely helpful in my marketing and I was surprised by the results I found.

Share and Enjoy:
  • Print
  • email
  • Digg
  • DZone
  • Twitter
  • Facebook
  • del.icio.us
  • Google Bookmarks
  • Reddit
  • Slashdot
  • Technorati
  • Yahoo! Buzz