Trying to group a query to display day event by hours

user616076 0
Viewed 32

I'm trying to get an account of a daily count from an Oracle query to display count by hours from 14:00 to 19:00. I'm using this query. I want to group the count output.

Select count(*), extract(hour from eventtime) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by hours

It fails where am I going wrong.

oracle sql
1 Answers

The hours alias is defined in the SELECT clause after the GROUP BY clause is evaluated so it cannot be used in the GROUP BY clause; use EXTRACT(hour from eventtime) instead.

Select count(*),
       extract(hour from eventtime) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by extract(hour from eventtime)

If your eventtime column is a DATE data-type then you cannot EXTRACT the hours field and need to cast it to a TIMESTAMP data-type:

Select count(*),
       extract(hour from CAST(eventtime AS TIMESTAMP)) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by extract(hour from CAST(eventtime AS TIMESTAMP))

fiddle

MT00
Related