Trying to group a query to display day event by hours-CodePudding

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.

CodePudding user response：

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