CODEWITHSUNDEEP
sqloracle-database
user149691
user149691

Reputation: 607

Optimize Group by Hour Query

Please help to optimize my query. It looks too bulky. I guess there is a better way to work with hours (datetime) in sql

There is a table dauditnew that is populated and population datetime is stored inside auditdate column.

Query returns rows count by hour. 

SELECT t.Hour, COUNT(t.Hour) as Count  FROM dauditnew d, 
  (SELECT 0 as Hour  FROM DUAL UNION 
   SELECT 1 FROM DUAL UNION 
   SELECT 2 FROM DUAL UNION 
   SELECT 3 FROM DUAL UNION 
   SELECT 4 FROM DUAL UNION 
   SELECT 5 FROM DUAL UNION 
   SELECT 6 FROM DUAL UNION 
   SELECT 7 FROM DUAL UNION 
   SELECT 8 FROM DUAL UNION 
   SELECT 9 FROM DUAL UNION 
   SELECT 10 FROM DUAL UNION 
   SELECT 11 FROM DUAL UNION 
   SELECT 12 FROM DUAL UNION 
   SELECT 13 FROM DUAL UNION 
   SELECT 14 FROM DUAL UNION 
   SELECT 15 FROM DUAL UNION 
   SELECT 16 FROM DUAL UNION 
   SELECT 17 FROM DUAL UNION 
   SELECT 18 FROM DUAL UNION 
   SELECT 19 FROM DUAL UNION 
   SELECT 20 FROM DUAL UNION 
   SELECT 21 FROM DUAL UNION 
   SELECT 22 FROM DUAL UNION 
   SELECT 23 FROM DUAL) t
where 
  d.auditdate >= TO_DATE('25.04.2017 ' || t.Hour, 'dd.mm.yyyy HH24') and 
  d.auditdate <= TO_DATE('25.04.2017 ' || t.Hour || '_59_59', 'dd.mm.yyyy HH24_MI_SS')
group by t.Hour

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to start with something like this:

select trunc(d.auditdate, 'HH24') as hh, count(*)
from dauditnew d
where d.auditdate >= '2017-04-25' and d.auditdate < '2017-04-26'
group by trunc(d.auditdate, 'HH24')
order by hh;

If this misses hours, then you can use a left join with this as a subquery.

Upvotes: 1

Related Questions