CODEWITHSUNDEEP
sqloracle-databasedatetimeaggregate-functionsdate-arithmetic
Dave
Dave

Reputation: 41

Average Date column in a query using Oracle SQL Developer v19

I have a query that as a result, amongst others, shows a column of ages, this age has been calculated from a 'Date' data type field. I would like to add an average age to the query for all records that meet the criteria.

I have been searching google with no success, when I try to cast I get an invalid datatype error

Any help would be much appreciated

SELECT P.First_name, P.Last_name, P.Sex, P.Ethnicity, 
Round((sysdate - P.D_O_B) / 365,0) as Age,
CAST( AVG(CAST(CAST(P.D_O_B as DateTime) as Float)) as DateTime)
From Patient P, Tests T
where T.Patient_ID = P.NHS_Number
and T.Result = 'Positive'
and T.date_of_test + 14 > SYSDATE
Group by P.first_name, P.Last_Name, P.Sex, P.Ethnicity, P.D_O_B
Order by Age desc;

Here is an example of what it looks like when I am not failing at calculating the Average Age

enter image description here

Upvotes: 1

Views: 482

Answers (2)

GMB
GMB

Reputation: 222412

Indeed a window function function is probably the what you are after, as Gordon Linoff points out. Using standard joins is also a must-have.

I would, however, recommend also fixing the date arithmetic; not all years have 365 days, so your query is off on leap years - and inaccuracy increases when a person gets older. Instead, I would recommend months_between():

select p.first_name, p.last_name, p.sex, p.ethnicity, 
    round(months_between(sysdate, p.d_o_b) / 12, 0) as age,
    round(avg(months_between(sysdate, p.d_o_b) / 12) over(), 0) as avg_age
from patient p 
inner join tests t on t.patient_id = p.nhs_number
where t.result = 'positive' and t.date_of_test + 14 > sysdate
group by p.first_name, p.last_name, p.sex, p.ethnicity, p.d_o_b
order by age desc;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You seem to want a window function:

select P.First_name, P.Last_name, P.Sex, P.Ethnicity, 
       Round((sysdate - P.D_O_B) / 365,0) as Age,
       round(avg((sysdate - P.D_O_B) / 365, 0) over (), 0) as avg_age
From Patient P join
     Tests T
     on T.Patient_ID = P.NHS_Number
where T.Result = 'Positive' and
      T.date_of_test + 14 > SYSDATE
Group by P.first_name, P.Last_Name, P.Sex, P.Ethnicity, P.D_O_B
Order by Age desc;

Note that I also fixed your archaic join syntax.

Upvotes: 1

Related Questions