Handy SQL snippet: Easily calculate average age of all members from DOB

The problem: Let’s say you have a database with a members or users list and you have the birthdate of these members stored in a date or datetime format (i.e. 1985-07-31 or similar). Now let’s say you would like to know the average age of the members in your list. This incidentally was exactly the problem I faced this afternoon. So what’s an easy way to do it in a matter of seconds? Simple once you have it figured it out.

If you have the list stored in a MySQL database, this little snippet will get the job done easily:

SELECT avg( (year( now() ) – year( [birthdate] ) ) + (DAYOFYEAR( now() ) > DAYOFYEAR( [birthdate] ) ) ) FROM [tablename]

Note: you need to replace [tablename] with the actual tablename (without the square brackets of course) and [birthdate] with the actual name of the column in your [tablename] in which you store the birthdate.

So what’s going on here?

Here’s the breakdown of what it’s doing:

Step 1 – We calculate each member’s age by subtracting the year we are now in from the year of their birth: year( now() ) – year( [birthdate] )
Step 2 – We check if as of right now, that person’s birthday has already passed, and if so, we will be adding 1 year to their age, if not, we don’t add a year: + ( DAYOFYEAR( now() ) > DAYOFYEAR([birthdate]) )
Step 3 – We are selecting the birthdays from the relevant table in the db, and with each record, doing the calculation and taking an average, i.e. the avg () portion

And that’s it!

I don’t remember offhand the syntax if you were doing this in another db, such as MS SQLServer or Postgres, but the syntax should be very similar (or exactly the same, just depending on whether those functions operate exactly the same in those dbs).