Home » SQL & PL/SQL » SQL & PL/SQL » Average a Date?
Average a Date? [message #243] Thu, 31 January 2002 15:05 Go to next message
John Carr
Messages: 7
Registered: January 2002
Junior Member
Hi,

I want to calculate the average age of patients on a year by year basis. How do you average a date in oracle?

I have the birthdate stored as a DATE field, and the range I want to average will be set by a juliandate I stored as a Number in the encounter table, as shown below:

Demographics (
PatientId VARCHar2, <pk>
DATEBIRTH DATE
)

Encounter (
PatientId Varchar2 <pk>
Encounternum Varchar2 <pk>
DayNUm Varchar2 <pk>
Juliandate NUMBER
)

Here is some code I wrote that I thought would work, but Oracle says I canít use average
select avg(d.datebirth), to_number(substr(to_char(e.juliandate), 1, 2))
from demographics d, encounter e
where d.patienid in
(select distinct patientid
from encounter
where juliandate between 78000 and 78999)
and e.patientid = d.patientid
group by to_number(substr(to_char(e.juliandate), 1, 2))

What Should I DO?! I am stuck.

Thanks

John
Re: Average a Date? [message #250 is a reply to message #243] Fri, 01 February 2002 00:26 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
To get the average age you have first of all have to get their age. To do this you have subtract the birthdate from the current date. Because the time unit in oracle is the day you have then to divide the result by 365.25. You can use this result to calculate the average age.

avg((sysdate-d.datebirth)/365.25)

HTH
Mike
Re: Average a Date? [message #264 is a reply to message #250] Fri, 01 February 2002 12:47 Go to previous message
John Carr
Messages: 7
Registered: January 2002
Junior Member
Thanks. However, if this is the sysdate, this is the age as of 2002. I want to calculate their age in 1975 (for example) and then average all the values to come up with the avergae age of the patient in the clinic for 1975.
Previous Topic: group by purgatory
Next Topic: CREATING A NEW SCHEMA?
Goto Forum:
  


Current Time: Sat Apr 04 23:04:26 CDT 2020