DJB DJB - 4 months ago 9x
SQL Question

Average age using months_between()

So I have a table with the birth dates and I need to average the people's age. How do I do that? I know I have to use months_between(). Thank you in advance!


Why do you think you need months_between? You don't (unless you have a very specific and unusual definition of "average age").

Over a long enough period (like 40+ years, say), a person's age in years can be calculated (within a narrow approximation window) as the age in days, divided by 365.25. The age in days is simply a difference between two dates, SYSDATE and DATE_OF_BIRTH or BORN. The first one is provided by the system and the second is in your table. Assuming, that is, that you want age as of today; otherwise change SYSDATE to whatever "as-of" (fixed) date you want to use.

So, something like

select [some columns here], AVG(SYSDATE - BORN)/365.25 as avg_age
from   your_table

Not clear why you would select max(born) from dual; surely you didn't call your table dual? Nor did you change the standard dual table to add your own data to it?

When people ask you what datatype you use for born in your tables, what you see on the screen when you query for it is not sufficient; the screen will show a string (it's the only thing a screen shows) and doesn't necessarily reflect what's in the database. To get the proper answer, run DESCRIBE table_name; that will show all the columns in table_name and their datatype. Note that DESCRIBE table_name is a SQL*Plus command (understood by Toad and SQL Developer - whatever you use to communicate with the database), so it doesn't need a ; or a / at the end. Just type it at the prompt and hit ENTER.

Good luck!