Hourly average for timestamps columns – FROM keyword not found where expected

I would like to get the hourly average of two columns. The type of the columns is timestamp.

I try this:

select trunc(endtime, 'HH') as Date,
    avg(extract(second from intrvl)
        + extract(minute from intrvl) * 60
        + extract(hour from intrvl) * 60 * 60
        + extract(day from intrvl) * 60 * 60 * 24) as average
from (
    select (endtime - starttime) intrvl 
    from mytable
)
group by Date;

I do not know if this is the right way to go at all. But the query is not correct. I get this error: 00923. 00000 - "FROM keyword not found where expected"

In the end I would like to get a table like this one:

Date average
16/11/2021 08:00 5.786
16/11/2021 09:00 8.996

How can I get this?

Answer

You need to include all the columns you are going to use in the outer-query in the SELECT clause of the inner-query and you cannot use an alias of the SELECT clause in the GROUP BY clause in the same sub-query:

select trunc(endtime, 'HH') as "Date",
       avg(
           extract(second from intrvl)
         + extract(minute from intrvl) * 60
         + extract(hour   from intrvl) * 60 * 60
         + extract(day    from intrvl) * 60 * 60 * 24
       ) as average
from (
  select endtime,
         endtime - starttime AS intrvl 
  from   mytable
)
group by trunc(endtime, 'HH');

---

If you do not have (or are not worried about) fractional seconds then you could also use:

select trunc(endtime, 'HH') as "Date",
       AVG(
         (CAST(endtime AS DATE) - CAST(starttime AS DATE))
         * 60 * 60 * 24
       ) AS average
from   mytable
group by trunc(endtime, 'HH');

db<>fiddle here