Home » SQL & PL/SQL » SQL & PL/SQL » work out age using two columns
work out age using two columns [message #284444] Thu, 29 November 2007 12:59 Go to next message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Hi All,

I have a table of people's data with date of birth column as well as Alive_Until (or date of Death)column and I am trying to work out the minimum and maximum age.

I have used Months_Between (sysdate, (min(DOB)) but this doesn’t give an accurate answer. I tried Months_Between (Alive_Until, min(DOB)) but its not accepting it saying "not a single-group group function"

Any help is appreciated Smile

this is a copy of what i have done:

SELECT TO_CHAR(TRUNC(MONTHS_BETWEEN(Alive_Until,min(DOB))/12))
||' YEARS '||
TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (Alive_Until, min(DOB)),12)))
||' MONTHS '||
TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN(Alive_Until,min(DOB))*30,31)))
||' DAYS' AGE
FROM EMP;

[Updated on: Thu, 29 November 2007 13:12]

Report message to a moderator

Re: work out age using two columns [message #284466 is a reply to message #284444] Thu, 29 November 2007 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
min(months_between(nvl(alive_until,sysdate),dob))

Regards
Michel
Re: work out age using two columns [message #284593 is a reply to message #284466] Fri, 30 November 2007 04:26 Go to previous message
Bashair
Messages: 10
Registered: November 2007
Junior Member
Thank you!!!
Previous Topic: problem in removing data?
Next Topic: format data with 4 decimal
Goto Forum:
  


Current Time: Tue Dec 06 16:02:37 CST 2016

Total time taken to generate the page: 0.05434 seconds