Today I Searched

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)
I modified the query to get the average age.
SELECT avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
I also wanted to see the average age for each sex.
SELECT sex, avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
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))
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))
This data will be extremely helpful in my marketing and I was surprised by the results I found.
Be Sociable, Share!
   

    




		


			


				
			

			
        

		  



		  

		  

		

	
 

  
Tweet